mysql數(shù)據(jù)庫(kù)約束

數(shù)據(jù)庫(kù)約束是對(duì)表中的數(shù)據(jù)進(jìn)行進(jìn)一步的限制，保證數(shù)據(jù)的正確性、有效性和完整性。約束用于限制加入表的數(shù)據(jù)的類型，可以在創(chuàng)建表時(shí)規(guī)定約束（通過 CREATE TABLE 語(yǔ)句），或者在表創(chuàng)建之后也可以（通過 ALTER TABLE 語(yǔ)句）

1、主鍵約束PRIMARY KEY

  • 每張數(shù)據(jù)表只能存在一個(gè)主鍵
  • 主鍵保證記錄的唯一性，主鍵的值不重復(fù)
  • 主鍵自動(dòng)為 NOT NULL
  • 可以使用一列作為主鍵，也可以使用兩列作為主鍵，稱之為符合主鍵，必須通過兩個(gè)列的數(shù)據(jù)才能進(jìn)行整體定位的表可以使用。
# 創(chuàng)建表的時(shí)候增加主鍵約束
mysql> create table user_key (
    -> id int(11),# id int(11) primary key,
    -> age int(3),
    -> name varchar(30),
    -> primary key (id));
Query OK, 0 rows affected (0.03 sec)

mysql> desc user_key;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| age   | int(3)      | YES  |     | NULL    |       |
| name  | varchar(30) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

# 給已存在列添加主鍵約束
mysql> alter table user_key modify id int(11) primary key; # alter table user_key add primary key(id);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc user_key;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| age   | int(3)      | YES  |     | NULL    |       |
| name  | varchar(30) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)


# 刪除主鍵約束
mysql> alter table user_key drop primary key;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc user_key;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | NULL    |       |
| age   | int(3)      | YES  |     | NULL    |       |
| name  | varchar(30) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

# 刪除NOT NULL約束
mysql> alter table user_key modify id int(11);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc user_key;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| age   | int(3)      | YES  |     | NULL    |       |
| name  | varchar(30) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

2、唯一約束UNIQUE KEY

  • 唯一約束可以保證記錄的唯一性
  • 唯一約束的字段可以為空值（NULL）
  • 每張數(shù)據(jù)表可以存在多個(gè)唯一約束
# 創(chuàng)建表的時(shí)候添加唯一約束
mysql> create table user_un(
    -> id int unique,
    -> name char(10));
Query OK, 0 rows affected (0.02 sec)

mysql> desc user_un;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | YES  | UNI | NULL    |       |
| name  | char(10) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

# 已存在的表添加唯一約束
# alter table user_un modify phone int unique;修改
mysql> alter table user_un add unique(name);   # 多個(gè)字段用逗號(hào)隔開
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc user_un;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | YES  | UNI | NULL    |       |
| name  | char(10) | YES  | UNI | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

# 刪除唯一約束
mysql> alter table user_un drop index phone;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc user_un;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | YES  | UNI | NULL    |       |
| name  | char(10) | YES  | UNI | NULL    |       |
| phone | int(11)  | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)

3、默認(rèn)約束DEFAULT

  • 當(dāng)插入記錄時(shí)，如果沒有明確為字段賦值，則自動(dòng)賦予默認(rèn)值
# 創(chuàng)建表的時(shí)候添加約束
mysql> create table user_de(
    -> id int primary key,
    -> name char(10) not null,
    -> age int not null default '0');  # age默認(rèn)為0
Query OK, 0 rows affected (0.03 sec)

mysql> desc user_de;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | NO   | PRI | NULL    |       |
| name  | char(10) | NO   |     | NULL    |       |
| age   | int(11)  | NO   |     | 0       |       |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)

# 給已存在的表添加默認(rèn)值約束
mysql> alter table user_de alter column address set default 'abc';
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc user_de;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id      | int(11)      | NO   | PRI | NULL    |       |
| name    | char(10)     | YES  |     | NULL    |       |
| age     | int(11)      | YES  |     | 1       |       |
| address | varchar(100) | YES  |     | abc     |       |
+---------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

# 修改默認(rèn)值
mysql> desc user_de;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id      | int(11)      | NO   | PRI | NULL    |       |
| name    | char(10)     | YES  |     | NULL    |       |
| age     | int(11)      | NO   |     | 3       |       |
| address | varchar(100) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> alter table user_de modify age int default '1';
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc user_de;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id      | int(11)      | NO   | PRI | NULL    |       |
| name    | char(10)     | YES  |     | NULL    |       |
| age     | int(11)      | YES  |     | 1       |       |
| address | varchar(100) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)


# 移除默認(rèn)值約束
# alter table user_de age int drop default;
mysql> alter table user_de modify name char(10);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc user_de;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id      | int(11)      | NO   | PRI | NULL    |       |
| name    | char(10)     | YES  |     | NULL    |       |
| age     | int(11)      | NO   |     | 0       |       |
| address | varchar(100) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

#添加一條數(shù)據(jù)不給age值，age默認(rèn)存儲(chǔ)0
mysql> insert into user_de (id,name) values (1,"張三");
Query OK, 1 row affected (0.00 sec)

mysql> select * from user_de;
+----+--------+-----+
| id | name   | age |
+----+--------+-----+
|  1 | 張三   |   0 |
+----+--------+-----+
1 row in set (0.00 sec)

4、非空約束NOT NULL

  • 強(qiáng)制列不能為 NULL 值，約束強(qiáng)制字段始終包含值
  • 這意味著，如果不向字段添加值，就無(wú)法插入新記錄或者更新記錄
# 創(chuàng)建表時(shí)增加非空約束
mysql> create table user_no(
    -> id int not null,
    -> name varchar(255),
    -> age int);
Query OK, 0 rows affected (0.02 sec)、

# 已存在的表增加非空約束
mysql> alter table user_no modify name varchar(255) not null;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

# 取消非空約束
mysql> alter table user_no modify name varchar(255) null;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

5、外鍵約束FOREIGN KEY

  • 保持?jǐn)?shù)據(jù)的一致性，完整性
  • 實(shí)現(xiàn)1對(duì)1或1對(duì)n關(guān)系
# 班級(jí)表
mysql> desc class;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(11)     | YES  |     | NULL    |       |
| name    | varchar(50) | YES  |     | NULL    |       |
| address | varchar(50) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
# 創(chuàng)建表時(shí)添加外鍵約束
mysql> create table student(
    -> id int,
    -> name varchar(50),
    -> c_id int,
    -> c_name varchar(50),
    -> constraint fk_cid foreign key(c_id) references class(id));
Query OK, 0 rows affected (0.03 sec)

# 已存在的表添加外鍵約束
mysql> ALTER TABLE stu ADD CONSTRAINT fk_cid FOREIGN KEY(c_id) REFERENCES class(id);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

# 刪除外鍵約束
# 先查看外鍵名
mysql> show create table stu;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                        |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| stu   | CREATE TABLE `stu` (
  `id` int(11) DEFAULT NULL,
  `name` char(50) DEFAULT NULL,
  `c_id` int(11) DEFAULT NULL,
  KEY `fk_cid` (`c_id`),
  CONSTRAINT `fk_cid` FOREIGN KEY (`c_id`) REFERENCES `class` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
# fk_cid為外鍵名稱
mysql> alter table student drop foreign key fk_cid; 
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

6、自增約束AUTO_INCREMENT

  • 可以約束任何一個(gè)字段
  • 不一定是PRIMARY KEY字段，也就是說自增的字段并不等于主鍵字段
  • 但是PRIMARY_KEY約束的主鍵字段，一定是自增字段，即PRIMARY_KEY 要與AUTO_INCREMENT一起作用于同一個(gè)字段
# 創(chuàng)建表示添加自增約束
mysql> create table zz(
    -> id int auto_increment primary key,
    -> name char(50));
Query OK, 0 rows affected (0.02 sec)

# 已存在表添加自增約束
mysql> alter table zz modify id int(11) auto_increment;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

# 刪除自增約束
mysql> desc zz;
+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          |
+-------+----------+------+-----+---------+----------------+
| id    | int(11)  | NO   | PRI | NULL    | auto_increment |
| name  | char(50) | YES  |     | NULL    |                |
+-------+----------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

mysql> alter table zz modify id int;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc zz;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | NO   | PRI | NULL    |       |
| name  | char(50) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請(qǐng)結(jié)合常識(shí)與多方信息審慎甄別。
平臺(tái)聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡(jiǎn)書系信息發(fā)布平臺(tái)，僅提供信息存儲(chǔ)服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容