最近对SQL的主键约束进行修改时忘记了代码,索性总结一下五种约束(除了非空约束)。
一、主键约束
1、创建表时主键约束
IF OBJECT_ID('test','U') IS NOT NULL
DROP TABLE test
CREATE TABLE test
(
ID INT NOT NULL CONSTRAINT PK_ID PRIMARY KEY,
NAME NCHAR(8)
)
或者创建联合主键
IF OBJECT_ID('test','U') IS NOT NULL
DROP TABLE test
CREATE TABLE test
(
ID INT NOT NULL ,
NAME NCHAR(8),
CONSTRAINT PK_ID PRIMARY KEY(ID,NAME)
)
2、添加主键约束
ALTER TABLE test
ADD CONSTRAINT PK_ID PRIMARY KEY(ID,NAME);
3、删除主键约束
ALTER TABLE test
DROP CONSTRAINT PK_ID #PK_ID为主键名称
二、唯一约束
唯一约束和主键约束有一点不同,主键约束同时也满足非空约束,而唯一约束不要求非空约束。
1、创建唯一约束
IF OBJECT_ID('test','U') IS NOT NULL
DROP TABLE test
CREATE TABLE test
(
ID INT NOT NULL CONSTRAINT UN_ID UNIQUE,
NAME NCHAR(8)
)
或者
IF OBJECT_ID('test','U') IS NOT NULL
DROP TABLE test
CREATE TABLE test
(
ID INT NOT NULL ,
NAME NCHAR(8),
CONSTRAINT UN_ID UNIQUE(ID)
)
2、添加唯一约束
ALTER TABLE test
ADD CONSTRAINT UN_ID UNIQUE(ID);
删除唯一约束同主键约束
三、检查约束
比如限制性别列的值只能为男或者女
IF OBJECT_ID('test','U') IS NOT NULL
DROP TABLE test
CREATE TABLE test
(
ID INT NOT NULL,
SEX NCHAR(8),
CONSTRAINT CK_SEX CHECK(SEX IN ('男','女'))
)
检查约束还可以和CASE WHEN 语句一起使用。比如添加约束条件男性只能小于25岁,女性只能小于23岁。
IF OBJECT_ID('test','U') IS NOT NULL
DROP TABLE test
CREATE TABLE test
(
ID INT NOT NULL,
SEX NCHAR(8), #性别
AGE INT, #年龄
CONSTRAINT CK_SEX CHECK(CASE WHEN SEX='男' AND AGE<25 THEN 1
WHEN SEX='女' AND AGE<23 THEN 1
ELSE 0 END=1 )
)
四、默认约束
1、创建默认约束
IF OBJECT_ID('test','U') IS NOT NULL
DROP TABLE test
CREATE TABLE test
(
ID INT NOT NULL,
SEX NCHAR(8) CONSTRAINT DF_SEX DEFAULT '女' #默认性别为女
)
2、添加默认约束
ALTER TABLE test
ADD CONSTRAINT DF_SEX DEFAULT '女' FOR SEX;
五、外键约束
(1)以公共关键字作主键的表为主键表(父表,主表)
(2)以公共关键字作外键的表为外键表(从表,外表)
#主键表
create table parent(
id number primary key,
name varchar(30),
);
#外键表
create table child(
name varchar(30),
fid number,
constraint child_fid_fk foreign key(fid) references parent(id)
on update cascade
on delete cascade,
);
On Delete和On Update都有Restrict,No Action, Cascade,Set Null属性。现在分别对他们的属性含义做个解释。
ON DELETE
restrict(约束):当在父表(即外键的来源表)中删除对应记录时,首先检查该记录是否有对应外键,如果有则不允许删除。
no action:意思同restrict.即如果存在从数据,不允许删除主数据。
cascade(级联):当在父表(即外键的来源表)中删除对应记录时,首先检查该记录是否有对应外键,如果有则也删除外键在子表(即包含外键的表)中的记录。
set null:当在父表(即外键的来源表)中删除对应记录时,首先检查该记录是否有对应外键,如果有则设置子表中该外键值为null(不过这就要求该外键允许取null)
ON UPDATE
restrict(约束):当在父表(即外键的来源表)中更新对应记录时,首先检查该记录是否有对应外键,如果有则不允许更新。
no action:意思同restrict.
cascade(级联):当在父表(即外键的来源表)中更新对应记录时,首先检查该记录是否有对应外键,如果有则也更新外键在子表(即包含外键的表)中的记录。
set null:当在父表(即外键的来源表)中更新对应记录时,首先检查该记录是否有对应外键,如果有则设置子表中该外键值为null(不过这就要求该外键允许取null)。