SQL完整性约束

  • 引言
  • 1.创建表时添加约束 ☆
  • 1）写法1：使用列级约束 √
  • 2）写法2：使用表级约束
  • 2.修改表时添加约束：Alter table ...
  • 3.删除约束：ALTER TABLE
  • 注意
  • 1.自增长列
  • 2.联合主键


引言

完整性约束：用于限制字段的值必须满足一定的条件，从而保证数据表中的数据的一致性和完整性！！！

1.常见的约束：

  • NOT NULL：非空 要求非空字段为必填项
  • DEFAULT：默认值 如果该字段不手动插入值，有默认值
  • PRIMARY KEY:主键 要求主键字段不能重复，且不能为空，一个表中只能有一个主键
  • UNIQUE:唯一 要求唯一键的字段不能重复，可以为空，一个表中可以有多个唯一键
  • CHECK:检查 mysql不支持，要求该字段必须满足指定的检查条件
  • FOREIGN KEY：外键 用于限制两个表的关系

2.约束的特性：

  • 只可以当做列级约束：非空、默认
  • 只可以当做表级约束：外键
  • 两者皆可：主键、唯一、检查

1.创建表时添加约束 ☆

例：

CREATE TABLE major(
	id INT PRIMARY KEY AUTO_INCREMENT,
	majorname VARCHAR(20)
);

1）写法1：使用列级约束 √

列级约束语法：字段声明 + 约束名

CREATE TABLE IF NOT EXISTS constraintTab(
	id INT PRIMARY KEY,		#主键
	NAME VARCHAR(20) NOT NULL UNIQUE,	#非空+唯一
	gender CHAR DEFAULT '男',	#默认
	age INT CHECK(age BETWEEN 18 AND 120),	#检查
	seat INT UNIQUE,#唯一
	majorId INT,

	CONSTRAINT fk FOREIGN KEY (majorid) REFERENCES major(id) 	#外键：fk约束名
);

2）写法2：使用表级约束

表级约束语法：约束类型(字段名)

[constraint 约束名] 约束类型(字段名) [references 主表(字段名)]

案例：

CREATE TABLE IF NOT EXISTS constraintTab(
	id INT,
	NAME VARCHAR(20) NOT NULL , # 非空
	gender CHAR DEFAULT '男',   # 默认
	age INT ,
	seat INT ,
	majorId INT,

	CONSTRAINT fk FOREIGN KEY (majorid) REFERENCES major(id),	#外键
	PRIMARY KEY(id),	#主键
	CONSTRAINT uq UNIQUE(seat),	#唯一
	CONSTRAINT ck CHECK(age BETWEEN 10 AND 100)	#检查
);

2.修改表时添加约束：Alter table …

创建案例表：

DROP TABLE IF EXISTS constraintTab;
CREATE TABLE IF NOT EXISTS constraintTab(
	id INT,
	NAME VARCHAR(20),
	gender CHAR,
	seat INT,
	age INT,
	majorid INT
);

1.添加主键：primary key

  • 1)当做列级约束
ALTER TABLE constraintTab MODIFY COLUMN id INT PRIMARY KEY;
  • 2)当做表级约束 √
ALTER TABLE constraintTab ADD PRIMARY KEY(id);

# 对比添加列：
ALTER TABLE stu ADD COLUMN classname varchar(100);

2.添加唯一键： UNIQUE

  • 1)当做列级约束
ALTER TABLE constraintTab MODIFY COLUMN seat INT UNIQUE;
  • 2)当做表级约束 √
ALTER TABLE constraintTab ADD UNIQUE(NAME);

3.添加检查约束：check

  • 1)当做列级约束
ALTER TABLE constraintTab 
MODIFY COLUMN age INT CHECK(age>=10 AND age<100);
  • 2)当做表级约束 √
ALTER TABLE constraintTab 
ADD CHECK(age>=10 AND age<100);

4.添加外键:foreign key

只能当做表级约束，所以使用添加字段定义的方式：add

ALTER TABLE constraintTab ADD 
CONSTRAINT fk_major_stuinfo 
FOREIGN KEY(majorid) REFERENCES major(id);

5.添加默认：default

只能当做列级约束，所以使用修改列的定义方式：modify

ALTER TABLE constraintTab MODIFY COLUMN gender CHAR DEFAULT '男';

6.添加非空：not null

只能当做列级约束，所以使用修改列的定义方式：modify

ALTER TABLE constraintTab MODIFY COLUMN name VARCHAR(20) NOT NULL;

3.删除约束：ALTER TABLE

1.删除主键：只有一个

ALTER TABLE constraintTab DROP PRIMARY KEY;

2.删除唯一：可以有多个，所以需指定字段名

ALTER TABLE constraintTab DROP INDEX seat;

3.删除外键： 可以有多个，所以需指定外键约束名

ALTER TABLE constraintTab DROP FOREIGN KEY constrainttab_ibfk_1;

4.删除非空：通过修改列属性

ALTER TABLE constraintTab MODIFY COLUMN name VARCHAR(20)  NULL;

5.删除默认：通过修改列属性

ALTER TABLE constraintTab MODIFY COLUMN gender CHAR(1) ;

注意

1.自增长列

  • 默认从1开始，步长为1
  • 1、自增长列要求设置的字段类型为数值型
  • 2、自增长列要求必须和键搭配使用
  • 3、一个表中最多有一个自增长列
  • 4、主键如果是自增长列（非空），添加记录时，可以选填

2.联合主键

创建表时，在constraint约束区域，声明指定字段为主键：

  • 格式：[constraint 名称] primary key (字段列表)
  • 关键字constraint可以省略，如果需要为主键命名，constraint不能省略，主键名称一般没用。
  • 字段列表需要使用小括号括住，如果有多字段需要使用逗号分隔。
  • 声明两个以上字段为主键，我们称为联合主键。

查看表的索引：

SHOW INDEX FROM constraintTab;