1 MySQL常用的几种约束:
(1)非空约束 NOT NULL;
(2) 主键约束 PRIMARY KEY;
(3)唯一约束 UNIQUE;
(4) 默认约束 DEFAULT;
(5) 外键约束 FOREIGN KEY;
2 非空约束:
语法规则:列名 数据类型 NOT NULL(在创建表的时候添加);
也可以通过修改表的方式添加非空约束:
ALTER TABLE bookinfo MODIFY book_name VARCHAR(20) NOT NULL;
删除非空约束:
ALTER TABLE bookinfo MODIFY book_name VARCHAR(20);
3 主键约束:
(1)单字段主键:
a. 在定义列的同时指定主键:
列名 数据类型 PRIMARY KEY;
b. 在列定义的后面指定主键:
[CONSTRAINT<约束名>] PRIMARY KEY(列名);
(2)多字段联合主键(复合主键):
a. 主键有多个字段(列)联合组成:
PRIMARY KEY(字段1, 字段2,…… , 字段n)
b. 通过修改表的方式添加主键:
ALTER TABLE bookinfo MODIFY book_id INT PRIMARY KEY;
ALTER TABLE bookinfo ADD PRIMARY KEY(book_id);
ALTER TABLE bookinfo ADD CONSTRAINT PK_ID PRIMARY KEY(book_id);
c. 删除主键:
ALTER TABLE bookinfo DROP PRIMARY KEY;
4 唯一约束:
语法规则:
a.在定义列的同时指定唯一约束:
列名 数据类型 UNIQUE
b. 在列定义的后面指定唯一约束:
[CONSTRAINT<约束名>] UNIQUE(列名)
c. 通过修改表的方式添加唯一约束:
ALTER TABLE bookinfo MODIFY book_name varchar(20) UNIQUE;
ALTER TABLE bookinfo ADD UNIQUE (book_id);
ALTER TABLE bookinfo ADD CONSTRAINT uk_bname UNIQUE (book_name);
d. 删除唯一约束:
ALTER TABLE bookinfo DROP INDEX uk_bname;(uk_bname表示唯一性约束的名称)
ALTER TABLE bookinfo DROP KEY uk_bname;
5 默认约束:
语法规则:
a.在定义列的同时指定默认约束:
列名 数据类型 DEFAULT 默认值;
b.通过修改表的方式添加唯一默认约束:
ALTER TABLE bookinfo MODIFY book_name varchar(20) DEFAULT 'xxxxx';
ALTER TABLE bookinfo ALTER COLUMN book_name SET DEFAULT 'xxxxx';
c. 删除默认约束:
ALTER TABLE bookinfo MODIFY book_name VARCHAR(20);
ALTER TABLE bookinfo ALTER COLUMN book_name DROP DEFAULT;
6 外键约束:
在外键约束里首先我们所要解决的就是如何确定父表与子表:
有外键是子表,外键去关联主键,一个主键可能对应多个表中的多个外键(就像一个父亲可以有多个儿子,而一个儿子只能有一个父亲)
外键对应参照完整性,一个表的外键可以为空值,若不为空,则每一个外键值必须等于另一个表中主键的某个值,一个表可以有多个外键。
a.外键约束的语法格式:
[CONSTRAINT] <外键约束的名称> FOREIGN KEY(列名)references <主表名>(主键)
b.修改表添加外键约束:
ALTER TABLE bookinfo ADD FOREIGN KEY(book_category_id) REFERENCES bookcategory(category_id);
alter table 子表的数据表名 add foreign key(子表的外键名称) references 父表的数据表名称(父表的主键名称)
c.删除外键约束:
ALTER TABLE bookinfo DROP FOREIGN KEY fk_id;(fk_id表示外键的名称)
CASCADE: 从父表中删除或更新对应的行,同时自动的删除或更新自表中匹配的行。
当使用ON DELETE CASCADE时候,如果父类的中的某个元素被删除,那么子类中与之涉及的元素也一并会被删除。
下面来看一些使用MySQL ON DELETE CASCADE
的例子。
假设有两张表:建筑物(buildings
)和房间(rooms
)。 在这个数据库模型中,每个建筑物都有一个或多个房间。 然而,每个房间只属于一个建筑物。没有建筑物则房间是不会存在的。
建筑物和房间表之间的关系是一对多(1:N),如下面的数据库图所示:
当我们从buildings
表中删除一行时,还要删除rooms
表中引用建筑物表中行的行。 例如,当删除建筑编号(building_no
)为2
的行记录时,在buildings
表上执行如下查询:
DELETE FROM buildings
WHERE
building_no = 2;
我们希望rooms
表中涉及到建筑物编号2
的行记录也将被删除(讲得通俗一点:假设2
号楼倒塌了,那么2
号楼的房间应该也就不存在了)。以下是演示MySQL ON DELETE CASCADE
参考操作如何工作的步骤。
第一步, 创建buildings
表,如下创建语句:
USE testdb;
CREATE TABLE buildings (
building_no INT PRIMARY KEY AUTO_INCREMENT,
building_name VARCHAR(255) NOT NULL,
address VARCHAR(255) NOT NULL
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
第二步, 创建rooms
表,如下创建语句:
USE testdb;
CREATE TABLE rooms (
room_no INT PRIMARY KEY AUTO_INCREMENT,
room_name VARCHAR(255) NOT NULL,
building_no INT NOT NULL,
FOREIGN KEY (building_no)
REFERENCES buildings (building_no)
ON DELETE CASCADE
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
请注意,在外键约束定义的末尾添加ON DELETE CASCADE
子句。
第三步, 将一些数据插入到buildings
表,如下插入语句:
INSERT INTO buildings(building_name,address)
VALUES('海南大厦','海口市国兴大道1234号'),
('万达水城','海口市大同路1200号');
第四步, 查询buildings
表中的数据:
mysql> select * from buildings;
+-------------+---------------+----------------------+
| building_no | building_name | address |
+-------------+---------------+----------------------+
| 1 | 海南大厦 | 海口市国兴大道1234号 |
| 2 | 万达水城 | 海口市大同路1200号 |
+-------------+---------------+----------------------+
2 rows in set
现在可以看到,在建筑物表中有两行记录。
第五步, 将一些数据插入到buildings
表,如下插入语句:
INSERT INTO rooms(room_name,building_no)
VALUES('Amazon',1),
('War Room',1),
('Office of CEO',1),
('Marketing',2),
('Showroom',2);
第六步, 查询rooms
表中的数据:
mysql> select * from rooms;
+---------+---------------+-------------+
| room_no | room_name | building_no |
+---------+---------------+-------------+
| 1 | Amazon | 1 |
| 2 | War Room | 1 |
| 3 | Office of CEO | 1 |
| 4 | Marketing | 2 |
| 5 | Showroom | 2 |
+---------+---------------+-------------+
5 rows in set
从上面行记录中可以看到,building_no=1的建筑有3
个房间,以及building_no=2有2
个房间。
第七步, 删除编号为2的建筑物:
DELETE FROM buildings WHERE building_no = 2;
第八步, 查询 rooms
表中的数据 -
mysql> DELETE FROM buildings WHERE building_no = 2;
Query OK, 1 row affected
mysql> SELECT * FROM rooms;
+---------+---------------+-------------+
| room_no | room_name | building_no |
+---------+---------------+-------------+
| 1 | Amazon | 1 |
| 2 | War Room | 1 |
| 3 | Office of CEO | 1 |
+---------+---------------+-------------+
3 rows in set
可以看到,表中只剩下引用building_no=1
的记录了,引用building_no=2
的所有行记录都被自动删除了。
请注意,
ON DELETE CASCADE
仅支持使用存储引擎支持外键(如InnoDB
)的表上工作。 某些表类型不支持诸如MyISAM
的外键,因此应该在使用MySQLON DELETE CASCADE
引用操作的表上选择适当的存储引擎。
查找受MySQL ON DELETE CASCADE操作影响的表的技巧
有时,当要从表中删除数据时,知道哪个表受到MySQL ON DELETE CASCADE
参考操作的影响是有用的。 可从information_schema
数据库中的referential_constraints
表中查询此数据,如下所示:
USE information_schema;
SELECT
table_name
FROM
referential_constraints
WHERE
constraint_schema = 'database_name'
AND referenced_table_name = 'parent_table'
AND delete_rule = 'CASCADE'
例如,要使用示例数据库(testdb
,因为上面两个表是建立在testdb
数据库之上的)中的CASCADE
删除规则查找与建筑表相关联的表,请使用以下查询:
USE information_schema;
SELECT
table_name
FROM
referential_constraints
WHERE
constraint_schema = 'testdb'
AND referenced_table_name = 'buildings'
AND delete_rule = 'CASCADE'
执行上面查询语句,得到以下结果 -
+------------+
| table_name |
+------------+
| rooms |
+------------+
1 row in set