MySQL創(chuàng)建表并添加約束

常見的約束

● 非空約束，not null
● 唯一約束，unique 
● 主鍵約束，primary key
● 外鍵約束，foreign key
● 自定義檢查約束，check（不建議使用）(在mysql中現(xiàn)在還不支持)

非空約束，not null

非空約束，針對某個字段設(shè)置其值不為空，如：學(xué)生的姓名不能為空。

drop table if exists t_student; 
create table t_student(
	student_id  	int(10),
	student_name 	varchar(20) not null,
	sex		char(2)  default  'm',
	birthday	date, 
	email		varchar(30),
	classes_id	int(3)	
)

insert into t_student(student_id, birthday, email, classes_id) 
values
(1002, '1988-01-01', '[email protected]', 10)

以上錯誤為加入的學(xué)生姓名為空。 

唯一約束，unique

唯一性約束，它可以使某個字段的值不能重復(fù)，如：email不能重復(fù)：

drop table if exists t_student; 
create table t_student(
	student_id  	int(10),
	student_name 	varchar(20) not null,
	sex		char(2)  default  'm',
	birthday	date, 
	email		varchar(30)  unique,
	classes_id	int(3)	
)
insert into t_student(student_id, student_name , sex, birthday, email, classes_id) 
values
(1001,'zhangsan','m', '1988-01-01', '[email protected]', 10)

以上插入了重復(fù)的email，所以出現(xiàn)了“違反唯一約束錯誤”，所以unique起作用了同樣可以為唯一約束起個約束名；

我們可以查看一下約束

mysql> use information_schema;

mysql> select * from table_constraints where table_name = 't_student';

關(guān)于約束名稱可以到table_constraints中查詢

以上約束的名稱我們也可以自定義。

drop table if exists t_student; 
create table t_student(
	student_id  	int(10),
	student_name 	varchar(20) not null,
	sex		char(2)  default  'm',
	birthday	date, 
	email		varchar(30)  ,
	classes_id	int(3)	,
constraint email_unique unique(email)/*表級約束*/
)

主鍵約束，primary key

每個表應(yīng)該具有主鍵，主鍵可以標(biāo)識記錄的唯一性，主鍵分為單一主鍵和復(fù)合（聯(lián)合）主鍵，單一主鍵是由一個字段構(gòu)成的，復(fù)合（聯(lián)合）主鍵是由多個字段構(gòu)成的。

drop table if exists t_student; 
create table t_student()
	student_id  	int(10)  primary key,/*列級約束*/
	student_name 	varchar(20) not null,
	sex		char(2)  default  'm',
	birthday	date, 
	email		varchar(30)  ,
	classes_id	int(3)	
)
insert into t_student(student_id, student_name , sex, birthday, email, classes_id) 
values
(1001,'zhangsan','m', '1988-01-01', '[email protected]', 10)

向以上表中加入學(xué)號為1001的兩條記錄，出現(xiàn)如下錯誤，因為加入了主鍵約束

我們也可以通過表級約束為約束起個名稱：

drop table if exists t_student; 
create table t_student(
	student_id  	int(10),
	student_name 	varchar(20) not null,
	sex		char(2)  default  'm',
	birthday	date, 
	email		varchar(30)  ,
	classes_id	int(3),
    CONSTRAINT p_id PRIMARY key (student_id)
)
insert into t_student(student_id, student_name , sex, birthday, email, classes_id) 
values
(1001,'zhangsan','m', '1988-01-01', '[email protected]', 10)

外鍵約束，foreign  key

外鍵主要是維護表之間的關(guān)系的，主要是為了保證參照完整性，如果表中的某個字段為外鍵字段，那么該字段的值必須來源于參照的表的主鍵，如：emp中的deptno值必須來源于dept表中的deptno字段值。

建立學(xué)生和班級表之間的連接

首先建立班級表t_classes

drop table if exists t_classes;
create table t_classes(
	classes_id int(3),
	classes_name varchar(40),
	constraint pk_classes_id primary key(classes_id)
)

在t_student中加入外鍵約束

drop table if exists t_student;
create table t_student(
	student_id  	int(10),
	student_name 	varchar(20),
	sex		char(2),
	birthday	date,
	email		varchar(30),
	classes_id	int(3),
	constraint      student_id_pk primary key(student_id),
constraint	fk_classes_id foreign key(classes_id) references t_classes(classes_id)	   
)

向t_student中加入數(shù)據(jù)

insert into t_student(student_id, student_name, sex, birthday, email, classes_id) values(1001, 'zhangsan', 'm', '1988-01-01', '[email protected]', 10)

出現(xiàn)錯誤，因為在班級表中不存在班級編號為10班級，外鍵約束起到了作用。

存在外鍵的表就是子表，參照的表就是父表，所以存在一個父子關(guān)系，也就是主從關(guān)系，主表就是班級表，從表就是學(xué)生表。

以上成功的插入了學(xué)生信息，當(dāng)時classes_id沒有值，這樣會影響參照完整性，所以我們建議將外鍵字段設(shè)置為非空。

drop table if exists t_student;
create table t_student(
	student_id  	int(10),
	student_name 	varchar(20),
	sex		char(2),
	birthday	date,
	email		varchar(30),
	classes_id	int (3) not null,
	constraint      student_id_pk primary key(student_id),
	constraint	fk_classes_id foreign key(classes_id) references t_classes(classes_id)	     
)
insert into t_student(student_id, student_name, sex, birthday, email, cla
sses_id) values(1001, 'zhangsan', 'm', '1988-01-01', '[email protected]', null);

再次插入班級編號為null的數(shù)據(jù)

添加數(shù)據(jù)到班級表，添加數(shù)據(jù)到學(xué)生表，刪除班級數(shù)據(jù)，將會出現(xiàn)如下錯誤：

insert into t_classes (classes_id,classes_name) values (10,'366');

insert into t_student(
student_id, student_name, sex, birthday, email, classes_id
) values(
1001, 'zhangsan', 'm', '1988-01-01', '[email protected]', 10
)

mysql> update t_classes set  classes_id = 20 where classes_name = '366';

因為子表(t_student)存在一個外鍵classes_id，它參照了父表(t_classes)中的主鍵，所以先刪除子表中的引用記錄，再修改父表中的數(shù)據(jù)。
我們也可以采取以下措施 級聯(lián)更新。

mysql> delete from t_classes where classes_id = 10;

因為子表(t_student)存在一個外鍵classes_id，它參照了父表(t_classes)中的主鍵，所以先刪除父表，那么將會影響子表的參照完整性，所以正確的做法是，先刪除子表中的數(shù)據(jù)，再刪除父表中的數(shù)據(jù)，采用drop table也不行，必須先drop子表，再drop父表。

我們也可以采取以下措施 級聯(lián)刪除。
級聯(lián)更新與級聯(lián)刪除
● on update cascade;

mysql對有些約束的修改比較麻煩，所以我們可以先刪除，再添加

alter table t_student drop foreign key fk_classes_id;

alter table t_student add constraint fk_classes_id_1 foreign key(classes_id) references t_classes(classes_id) ?on update cascade;

我們只修改了父表中的數(shù)據(jù)，但是子表中的數(shù)據(jù)也會跟著變動。

● on delete cascade; 

mysql對有些約束的修改時不支持的，所以我們可以先刪除，再添加

alter table t_student drop foreign key fk_classes_id;

alter table t_student add constraint fk_classes_id_1 foreign key(classes_id) references t_classes(classes_id) ?on delete cascade;
delete from t_classes where classes_id = 20;