create table teacher( ...> id int primary key not null, ...> name char (20) not null, ...> class int );

sqlite> insert into teacher(
   ...> id,name)values(1,"34");
sqlite> select * from teacher;
id          name        class     
----------  ----------  ----------
1           34                    

DEFAULT 约束

DEFAULT 约束在 INSERT INTO 语句没有提供一个特定的值时，为列提供一个默认值。

sqlite> create table teacher(
   ...> id int primary key not null,
   ...> name char (20) not null,
   ...> age int not null,
   ...> class int default 1);
sqlite> insert into teacher(id,name,age)values(1,"aa",34);
sqlite> select * from teacher;
id          name        age         class     
----------  ----------  ----------  ----------
1           aa          34          1         
sqlite> 

UNIQUE 约束

UNIQUE 约束防止在一个特定的列存在两个记录具有相同的值。在 COMPANY 表中，例如，您可能要防止两个或两个以上的人具有相同的年龄。

sqlite> create table teacher(
   ...> id int primary key not null,
   ...> name char (20) not null,
   ...> age int not null,
   ...> class int unique);
sqlite> insert into teacher(id,name,age,class)values(1,"aa",34,1);
sqlite> select * from teacher;
id          name        age         class     
----------  ----------  ----------  ----------
1           aa          34          1         
sqlite> insert into teacher(id,name,age,class)values(2,"aa",34,1);
Error: UNIQUE constraint failed: teacher.class
sqlite> 

CHECK 约束

CHECK 约束启用输入一条记录要检查值的条件。如果条件值为 false，则记录违反了约束，且不能输入到表。

sqlite> create table teacher(
   ...> id int primary key not null,
   ...> name char(20) not null,
   ...> age int check(age<100 and age >0));
sqlite> insert into teacher(id,name,age)values(1,"aa",34);
sqlite> select * from teacher;
id          name        age       
----------  ----------  ----------
1           aa          34        
sqlite> insert into teacher(id,name,age)values(2,"bb",101);
Error: CHECK constraint failed: teacher
sqlite> insert into teacher(id,name,age)values(2,"bb",0);
Error: CHECK constraint failed: teacher
sqlite> 

 

 

sqlte3 的约束

标签：