mysql數據庫學習——4,完整性約束

mysql資料庫學習——4,完整性約束

 

主鍵

create table feng(
teamno int not null,
playerno int not null,
pision char(6) not null,
primary key(teamno)
)


create table feng(

teamno int not null primary key ,
playerno int not null,
pision char(6) not null,
)

 

 

復合主鍵

create table feng(
teamno int not null,
playerno int not null,
pision char(6) not null,
primary key(teamno,playerno)
)

 

 

替代鍵(候選鍵)

 

create table feng(
teamno int not null primary key ,
playerno int not null,
pision char(6) not null,
unique(playerno)
)

create table feng(
teamno int not null primary key ,
playerno int not null,
pision char(6) not null,
unique(playerno,pision)
)

 

 

外鍵(在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,
pision char(6) not null,
foreign key(pision)
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)
)

 

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。