mysql数据库学习——4，完整性约束

主键

create table feng(

teamno int not null,

playerno int not null,

division char(6) not null,

primary key(teamno)

)

 

 

create table feng(

teamno int not null primary key ,

playerno int not null,

division char(6) not null,

)

 

复合主键

create table feng(

teamno int not null,

playerno int not null,

division char(6) not null,

primary key(teamno，playerno)

)

 

替代键（候选键）

 

create table feng(

teamno int not null primary key ,

playerno int not null,

division char(6) not null,

unique(playerno)

)

 

create table feng(

teamno int not null primary key ,

playerno int not null,

division char(6) not null,

unique(playerno，division)

)

 

外键（在innoDB中使用）

外键声明包括三个部分

1，那个列或列组合是外键

2，指定外键参照的表和列

3，参照动作[cascade（级联操作）,restrict（拒绝操作）,set null（设为空）,no action,set default]

如果没有指定参照动作默认是

on update restrict

on delete  restrict

create table feng(

teamno int not null primary key ,

playerno int not null,

division char(6) not null,

foreign key（division）

references othertable   （column）

on update restrict

unique(playerno)

)

 

check完整性约束

create  table players 

(playerno int not null,

 sex   char(1) not null,

         check (sex in ('m','f'))

)

 

create  table players 

(playerno int not null,

birth_date  date,

 sex   char(1) not null,

         check (sex in ('m','f'))

joined smallint not null,

             check (year(birth_date)<joined),

             check (joined<1880),

)

 

create  table players 

(playerno int not null,

birth_date  date,

 sex   char(1) not null,

          check (sex in (select sex from wholetab)

)