数据库(Mysql)----mysql建表中的约束

今天药忘吃喽~ 2021-09-23 13:16 367阅读 0赞

## **1.主键约束** ##

**它能够唯一确定一张表中的一条记录,增加主键约束之后,就可以使得字段不重复而且不为空**

create table user(
        id int PRIMARY KEY,
        name VARCHAR(20)
    );

INSERT INTO user VALUES (1,‘张三’);

+----+------+
    | id | name |
    +----+------+
    |  1 | 张三 |
    +----+------+

运行DESCRIBE user;

+-------+-------------+------+-----+---------+-------+
    | Field | Type        | Null | Key | Default | Extra |
    +-------+-------------+------+-----+---------+-------+
    | id    | int(11)     | NO   | PRI | NULL    |       |
    | name  | varchar(20) | YES  |     | NULL    |       |
    +-------+-------------+------+-----+---------+-------+

发现 id是不可以为null 而且 key的值 也变为:PRI(primary)

## 2.复合主键: ##

**复合主键只要所有的字段都不是相同的情况下可以允许其中的字段重复**

CREATE TABLE user2(
        id INT,
        name VARCHAR(20),
        password VARCHAR(20),
        PRIMARY key(id,name)
    );

运行DESCRIBE user2;

+----------+-------------+------+-----+---------+-------+
    | Field    | Type        | Null | Key | Default | Extra |
    +----------+-------------+------+-----+---------+-------+
    | id       | int(11)     | NO   | PRI | NULL    |       |
    | name     | varchar(20) | NO   | PRI | NULL    |       |
    | password | varchar(20) | YES  |     | NULL    |       |
    +----------+-------------+------+-----+---------+-------+

INSERT INTO user2 VALUES (1,‘老王’,‘123456’);  
INSERT INTO user2 VALUES (2,‘老王’,‘123456’);

+----+------+----------+
    | id | name | password |
    +----+------+----------+
    |  1 | 老王 | 123456   |
    |  2 | 老王 | 123456   |
    +----+------+----------+

INSERT INTO user2 VALUES (1,‘老李’,‘123456’);

SELECT \* FROM user2;

+----+------+----------+
    | id | name | password |
    +----+------+----------+
    |  1 | 老李 | 123456   |
    |  1 | 老王 | 123456   |
    |  2 | 老王 | 123456   |
    +----+------+----------+

场景:表中有班级号以及学生座位号,我们可以用班级号+学生的座位号可以准确的定位一个学生,如:(1班5号可以准确的确定一个学生)

## 3.自增约束: ##

**当对应的字段，不给值，会自动的被系统触发，系统会从当前字段中已经有的最大值+1操作， 得到一个新的不同的值。**

CREATE TABLE user3(
        id INT PRIMARY KEY AUTO_INCREMENT,
        name VARCHAR(20)
    );

运行DESCRIBE user3;

+-------+-------------+------+-----+---------+----------------+
    | Field | Type        | Null | Key | Default | Extra          |
    +-------+-------------+------+-----+---------+----------------+
    | id    | int(11)     | NO   | PRI | NULL    | auto_increment |
    | name  | varchar(20) | YES  |     | NULL    |                |
    +-------+-------------+------+-----+---------+----------------+

INSERT INTO user3(name) VALUES(‘张三’);  
INSERT INTO user3(name) VALUES(‘李四’);

+----+------+
    | id | name |
    +----+------+
    |  1 | 张三 |
    |  2 | 李四 |
    +----+------+

没有自定义id值 但是自动生成了id

## 4.唯一约束: ##

CREATE TABLE user5(
        id INT PRIMARY KEY AUTO_INCREMENT,
        name VARCHAR(20)
    );

运行 DESCRIBE user5;

新增name为唯一约束:  
ALTER TABLE user5 ADD UNIQUE(name);  
运行 DESCRIBE user5;

+-------+-------------+------+-----+---------+----------------+
    | Field | Type        | Null | Key | Default | Extra          |
    +-------+-------------+------+-----+---------+----------------+
    | id    | int(11)     | NO   | PRI | NULL    | auto_increment |
    | name  | varchar(20) | YES  | UNI | NULL    |                |
    +-------+-------------+------+-----+---------+----------------+

测试:插入数据  
INSERT INTO user5(name) VALUES (‘cc’);  
运行 SELECT \* FROM user5; 查看结果:

+----+------+
    | id | name |
    +----+------+
    |  1 | cc   |
    +----+------+

再次插入INSERT INTO user5(name) VALUES (‘cc’);  
出现:ERROR 1062 (23000): Duplicate entry ‘cc’ for key ‘name’

换个试试 INSERT INTO user5(name) VALUES (‘aa’);  
运行 SELECT \* FROM user5; 查看结果:

+----+------+
    | id | name |
    +----+------+
    |  3 | aa   |
    |  1 | cc   |
    +----+------+

**总结一下:**  
主键约束(primary key)中包含了唯一约束  
场景:业务需求:设计一张用户注册表,用户姓名必须要用手机号来注册,而且手机号和用户名称都不能为空,那么:

CREATE TABLE user_test(
        id INT PRIMARY KEY AUTO_INCREMENT COMMENT'主键id',
        name VARCHAR(20) NOT NULL COMMENT'用户姓名,不能为空',
        phone_number VARCHAR(20) UNIQUE NOT NULL COMMENT'用户手机,不能重复且不能为空'
    );

运行 DESCRIBE user\_test;

+--------------+-------------+------+-----+---------+----------------+
    | Field        | Type        | Null | Key | Default | Extra          |
    +--------------+-------------+------+-----+---------+----------------+
    | id           | int(11)     | NO   | PRI | NULL    | auto_increment |
    | name         | varchar(20) | NO   |     | NULL    |                |
    | phone_number | int(11)     | NO   | UNI | NULL    |                |
    +--------------+-------------+------+-----+---------+----------------+

这样的话就达到了每一个手机号都只能出现一次,达到了每个手机号只能被注册一次.  
用户姓名可以重复,但是手机号码却不能重复,复合正常的逻辑需求

## 5.非空约束:(NUT NULL) ##

**在上面的总计中已经添加了非空约束: NOT NULL;**

name和phone\_number都设置了非空,先只设置name参数不设置phone\_number参数试一试  
INSERT INTO user\_test (name) VALUES (‘张三’);  
会出现Field ‘phone\_number’ doesn’t have a default value

两个非空参数一起设置:  
INSERT INTO user\_test (name,phone\_number) VALUES (‘张三’,‘12345678901’);

+----+------+--------------+
    | id | name | phone_number |
    +----+------+--------------+
    |  1 | 张三 | 12345678901  |
    +----+------+--------------+

## 6.默认约束 ##

CREATE TABLE user6(
       id int PRIMARY KEY AUTO_INCREMENT COMMENT'主键id',
        name VARCHAR(20) NOT NULL COMMENT'用户姓名不能为空',
        phone_number VARCHAR(20) NOT NULL COMMENT'用户手机号,不能为空',
        status INT DEFAULT 0 COMMENT'用户状态0:启用 1:禁封 默认:0'
    );

运行DESCRIBE user6;

+--------------+-------------+------+-----+---------+----------------+
    | Field        | Type        | Null | Key | Default | Extra          |
    +--------------+-------------+------+-----+---------+----------------+
    | id           | int(11)     | NO   | PRI | NULL    | auto_increment |
    | name         | varchar(20) | NO   |     | NULL    |                |
    | phone_number | varchar(20) | NO   |     | NULL    |                |
    | status       | int(11)     | YES  |     | 0       |                |
    +--------------+-------------+------+-----+---------+----------------+

插入数据:  
INSERT INTO user6(name,phone\_number) VALUES (‘aa’,‘123’);  
INSERT INTO user6(name,phone\_number) VALUES(‘bb’,‘1234’);  
INSERT INTO user6(name,phone\_number) VALUES(‘cc’,‘1263456’);

查看数据:SELECT \* FROM user6;

+----+------+--------------+--------+
    | id | name | phone_number | status |
    +----+------+--------------+--------+
    |  1 | aa   | 123          |      0 |
    |  2 | bb   | 1234         |      0 |
    |  3 | cc   | 1263456      |      0 |
    +----+------+--------------+--------+

我们没有设置status的值,但是给我们创建了默认值 0.

**应用场景:**  
业务需求:找正常的用户,对这些正常用户进行发放优惠卷或者积分之类的东西,而被禁封的用户我们不让其参加多动.  
我们想要封用户只要将status的值从0改为1就行了,当然我们取用户的时候必须要先判断status是否是0.若是1.说明该用户已经被禁封.  
先封手机号为’1234’的用户:  
UPDATE user6 SET status = 1 WHERE phone\_number= ‘1234’;  
SELECT \* FROM user6;

+----+------+--------------+--------+
    | id | name | phone_number | status |
    +----+------+--------------+--------+
    |  1 | aa   | 123          |      0 |
    |  2 | bb   | 1234         |      1 |
    |  3 | cc   | 1263456      |      0 |
    +----+------+--------------+--------+

status为1,说明用户已经被封,该用户不可以参加活动  
我们取用户的时候加上status的判断,如:  
SELECT \* FROM user6 WHERE status = 0;

+----+------+--------------+--------+
    | id | name | phone_number | status |
    +----+------+--------------+--------+
    |  1 | aa   | 123          |      0 |
    |  3 | cc   | 1263456      |      0 |
    +----+------+--------------+--------+

## 7.外键约束 ##

CREATE TABLE classes(
        id INT PRIMARY KEY AUTO_INCREMENT COMMENT'班级表id',
        name VARCHAR(20) COMMENT'班级名称'
    );

运行DESCRIBE classes;

CREATE TABLE student(
       id INT PRIMARY KEY AUTO_INCREMENT COMMENT'学生表id',
       name VARCHAR(20) COMMENT'学生姓名',
        class_id int COMMENT'教室id,这张表中的class_id是classes表中id的值',
        FOREIGN KEY (class_id) REFERENCES classes(id)
    );

//FOREIGN :外来 REFERENCES:应用,参考  
运行DESCRIBE student;

+----------+-------------+------+-----+---------+----------------+
    | Field    | Type        | Null | Key | Default | Extra          |
    +----------+-------------+------+-----+---------+----------------+
    | id       | int(11)     | NO   | PRI | NULL    | auto_increment |
    | name     | varchar(20) | YES  |     | NULL    |                |
    | class_id | int(11)     | YES  | MUL | NULL    |                |
    +----------+-------------+------+-----+---------+----------------+

**班级插入数据:**

INSERT INTO CLASSES (name) VALUES (‘一班’);  
INSERT INTO CLASSES (name) VALUES (‘二班’);  
INSERT INTO CLASSES (name) VALUES (‘三班’);  
INSERT INTO CLASSES (name) VALUES (‘四班’);

查看数据 SELECT \* FROM classes;

+----+------+
    | id | name |
    +----+------+
    |  1 | 一班 |
    |  2 | 二班 |
    |  3 | 三班 |
    |  4 | 四班 |
    +----+------+

**学生插入数据:**  
INSERT INTO student (name,class\_id) VALUES (‘小赵’,1);  
INSERT INTO student (name,class\_id) VALUES (‘小钱’,2);  
INSERT INTO student (name,class\_id) VALUES (‘小孙’,3);  
INSERT INTO student (name,class\_id) VALUES (‘小李’,4);  
查看数据 SELECT \* FROM student;

+----+------+----------+
    | id | name | class_id |
    +----+------+----------+
    |  1 | 小赵 |        1 |
    |  2 | 小钱 |        2 |
    |  3 | 小孙 |        3 |
    |  4 | 小李 |        4 |
    +----+------+----------+

若是像插入班级为5的数据 如:  
INSERT INTO student (name,class\_id) VALUES (‘小周’,5);  
报错: Cannot add or update a child row  
我们删除正在被学生表引用的’四班’试试:  
DELETE classes WHERE name = ‘四班’;  
出现:Cannot delete or update a parent row:不能删除主表中的行

我们先删除学生表中的 '小李'从而解除班级中'四班'的外键约束,再来删除'四班'(因为小李引用了四班)
    DELETE FROM student WHERE name = '小李';
    再次删除classes表中的'四班';
    DELETE FROM classes WHERE name = '四班';
    最后: SELECT * FROM classes;
    +----+------+
    | id | name |
    +----+------+
    |  1 | 一班 |
    |  2 | 二班 |
    |  3 | 三班 |
    +----+------+
    '四班'被成功删除!

**总结**  
1.主表中没有的数据,在附表中,是不可以使用的.  
2.主表中记录的数据现在正在被附表所引用,那么主表中正在被引用的数据不可以被删除  
3.若要想删除,先将附表中的数据删除在删除主表数据  
4.对于外键约束大家可以联想 省,市 来进行联想 (市必须要依赖于省,只要省还有一个市在引用,那么就不可以删除省,要不然市就没有省了. 那么我们想删除省,必须要将该省下所有的市全部删除之后,才可以删除这个省)

## 8.如何建表之后添加主键约束 ##