本文介绍: 在MySQL里,“约束”指的是对表中数据的一种限制约束,它能够确保数据库数据的准确性和有效比如有的数据必填项,就像身份认证时候,或者填注册信息时候手机号身份证这种就不能空着,所以就有了非空约束;又有的数据比如用户唯一id,不能跟其他人的一样,所以就需要使用唯一约束等等。在MySQL中主要有6种约束:主键约束、外键约束、唯一约束、检查约束、非空约束和默认值约束。在建立数据表时候,一般情况下,为了方便更快地查找表中记录,都会要求在表中设置一个主键”。

目录

一. 主键约束——PRIMARY KEY

1.在建表时设置主键约束

设置单字段主键

设置联合主键

2. 在修改表的时候添加主键约束

3. 删除主键约束

4. 【补充】 MySQL设置主键自增长 :

二. 外键约束——FOREIGN KEY

1.在创建表时设置外键约束

2. 在修改表时添加外键约束

3. 删除外键约束

三. 唯一约束——UNIQUE KEY

1.在创建表时设置唯一约束

2. 在修改表时添加唯一约束

3. 删除唯一约束

四. 检查约束——CHECK

1.在创建表时设置检查约束

2. 在修改表时添加检查约束

3. 删除检查约束

五. 非空约束——NOT NULL

1.在创建表时设置非空约束

2. 在修改表时添加非空约束:

3. 删除非空约束

六. 默认值约束——DEFAULT

1.在创建表时设置默认值约束

2. 在修改表时添加默认值约束

3. 删除默认值


在MySQL里,“约束”指的是对表中数据的一种限制约束,它能够确保数据库中数据的准确性和有效

比如有的数据是必填项,就像身份认证时候,或者填注册信息时候手机号身份证这种就不能空着,所以就有了非空约束;又有的数据比如用户唯一id,不能跟其他人的一样,所以就需要使用唯一约束等等。

在MySQL中主要有6种约束:主键约束、外键约束、唯一约束、检查约束、非空约束和默认值约束。

一. 主键约束——PRIMARY KEY

主键约束“是6种约束中使用最多的约束。

在建立数据表时候,一般情况下,为了方便更快地查找表中的记录,都会要求在表中设置一个主键”。”主键“是表里面一个特殊字段这个字段能够唯一标识该表中的每条信息。

主键分为单字主键”和“多字段联合主键”,并且在使用主键的时候需要注意以下几个点:

1.在建表设置主键约束
设置单字段主键

1)在定义字段时候设置主键约束,语法格式

<字段名> <数据类型> PRIMARY KEY [默认值]

例:数据库创建学生信息数据表st_info,主键为st_id, SQL语句以及运行结果如下

create table st_info(
st_id int(10) primary key,
  name varchar(20),
  class varchar(10),
  gender varchar(4),
  age int(2)
);

mysql> desc st_info;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| st_id | int | NO | PRI | NULL | |
| name | varchar(20) | YES |     | NULL |       |
| class | varchar(10) | YES | | NULL   | |
| gender | varchar(4) | YES |     | NULL |       |
| age | int | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

2)在定义完所有字段之后指定部件语法格式

[CONSTRAINT <约束名>] PRIMARY KEY [字段名]

 在数据库中创建学生信息数据表st_info2,主键为st_id, SQL语句以及运行结果如下

create table st_info2(
st_id int(10),
  name varchar(20),
  class varchar(10),
  gender varchar(4),
  age int(2),
   primary key(st_id)
);

mysql> desc st_info2;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| st_id | int | NO | PRI | NULL | |
| name | varchar(20) | YES |     | NULL |       |
| class | varchar(10) | YES | | NULL   | |
| gender | varchar(4) | YES |     | NULL |       |
| age | int | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
设置联合主键

联合主键即一张表的主键由多个字段组成。

比如学生信息表中,可以设置st_id跟name来作为联合主键,语法格式以及SQL语句实现为:

PRIMARY KEY [字段1,字段2,…,字段n]

create table st_info3(
  st_id int(10),
  name varchar(20),
  class varchar(10),
  gender varchar(4),
  age int(2),
   primary key(st_id,name)
);

mysql> desc st_info3;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| st_id | int | NO | PRI | NULL | |
| name | varchar(20) | NO | PRI | NULL |       |
| class | varchar(10) | YES | | NULL   | |
| gender | varchar(4) | YES |     | NULL |       |
| age | int | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

需要注意的是,在设置联合主键的时候,不能在每个字段名后面直接声明主键约束。

2. 在修改表的时候添加主键约束

如果在创建表的时候没有设置主键约束,还可以修改表时进行添加,但是设置成主键约束的字段不允许有空值语法格式

ALTER TABLE <数据表名> ADD PRIMARY KEY(<字段名>);

mysql> desc st_info4;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| st_id | int | NO | | NULL | |
| name | varchar(20) | NO |     | NULL |       |
| class | varchar(10) | YES | | NULL   | |
| gender | varchar(4) | YES |     | NULL |       |
| age | int | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+

st_idname设置成主键:

mysql> alter table st_info4 add primary key(st_id,name);
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0  Warnings: 0

mysql> desc st_info4;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| st_id | int | NO | PRI | NULL | |
| name | varchar(20) | NO | PRI | NULL |       |
| class | varchar(10) | YES | | NULL   | |
| gender | varchar(4) | YES |     | NULL |       |
| age | int | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
3. 删除主键约束

一个表中不需要主键约束时,就需要从表中将其删除

 删除st_info4数据表中的主键约束:

mysql> alter table st_info4 drop primary key;
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0  Warnings: 0

mysql> desc st_info4;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| st_id | int | NO | | NULL | |
| name | varchar(20) | NO |     | NULL |       |
| class | varchar(10) | YES | | NULL   | |
| gender | varchar(4) | YES |     | NULL |       |
| age | int | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
4. 【补充】 MySQL设置主键自增长 :

在MySQL里,当主键定义自增长后,主键的值就不需要自己输入数据了,而是由数据库系统根据定义自动赋值,每增加一记录,主键就会自动根据设置的步长进行增长

在MySQL中自增长的关键字AUTO_INCREMENT语法格式为:

字段名 数据类型 AUTO_INCREMENT

 创建学生信息表st_info5,指定st_id字段自增

create table st_info5(
  st_id int(10) primary key auto_increment,
    name varchar(20) not null,
    class varchar(10),
    gender varchar(4),
    age int(2)
);

此时可以不用再手动插入st_id的数据:

insert into st_info5(name,class,gender,age) 
values('王思瑶','管理学1班','女',18),
      ('王绮梦','管理学1班','女',18),
      ('李若彤','管理学1班','男',18);
      
mysql> select * from st_info5;
+-------+--------+-----------+--------+------+
| st_id | name | class     | gender | age |
+-------+--------+-----------+--------+------+
| 1 | 王思瑶 | 管理学1班 | 女 | 18 |
| 2 | 王绮梦 | 管理学1班 | 女 | 18 |
| 3 | 李若彤 | 管理学1班 | 男 | 18 |
+-------+--------+-----------+--------+------+
3 rows in set (0.01 sec)

但是在设置学号的时候不一定是从1开始的,比如要从2000150001开始,则需要在建表之后设置自增的起始数据:

create table st_info6(
    st_id int(10) primary key auto_increment,
    name varchar(20) not null,
    class varchar(10),
    gender varchar(4),
    age int(2)
)auto_increment=2000150001;

insert into st_info6(name,class,gender,age) 
values('王思瑶','管理学1班','女',18),
      ('王绮梦','管理学1班','女',18),
      ('李若彤','管理学1班','男',18);
      
mysql> select * from st_info6;
+------------+--------+-----------+--------+------+
| st_id | name | class     | gender | age |
+------------+--------+-----------+--------+------+
| 2000150001 | 王思瑶 | 管理学1班 | 女 | 18 |
| 2000150002 | 王绮梦 | 管理学1班 | 女 | 18 |
| 2000150003 | 李若彤 | 管理学1班 | 男 | 18 |
+------------+--------+-----------+--------+------+
3 rows in set (0.00 sec)

值得注意的地方是,如果在插入一行数据产生了报错,则最终自增字段可能出现不连续的情况。

二. 外键约束——FOREIGN KEY

外键约束是表的一个特殊字段,普遍会和主键约束一起使用用来确保数据的一致性

对于两个具有关联关系的表来说,相关联字段中主键所在的表就是主表(父表),外键所在的表就是从表(子表)。

所以外键就是用来建立主表与从表的关联关系,为两个表的数据建立连接,约束两个表中数据的一致性完整性。

定义外键时需要遵守以下规则

1.在创建表时设置外键约束

建表语句中,可以加入关键字FOREIGN KEY来指定外键,用REFERENCES连接与主表的关系语法格式:

CONSTRAINT <约束名>
FOREIGN KEY <外键名>(字段名1,字段名2...)
REFERENCES <主表名>(主键字段名)

例:

使用1部分创建的st_info5数据表作为主表:

mysql> desc st_info5;
+--------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+----------------+
| st_id | int | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO |     | NULL |                |
| class  | varchar(10) | YES  | | NULL    | |
| gender | varchar(4) | YES |     | NULL |                |
| age | int | YES | | NULL | |
+--------+-------------+------+-----+---------+----------------+

② 创建tb_1数据表,并在表上创建外键约束,使其中course_id作为外键关联到表st_info5的主键st_id

create table tb_1(
    course_id int(8) not null auto_increment,
    course_name varchar(25) not null,
    constraint course_Choosing
    foreign key fk_course(course_id)
    references st_info5(st_id)
)auto_increment=20015001;

mysql> desc tb_1;
+-------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+----------------+
| course_id | int | NO | MUL | NULL | auto_increment |
| course_name | varchar(25) | NO |     | NULL |                |
+-------------+-------------+------+-----+---------+----------------+

上面语句执行成功之后,在表tb_1添加course_Choosing的约束名称,以及外键名称fk_coursecourse_id字段,依赖于st_info5的主键st_id

2. 在修改表时添加外键约束

同样的可以在创建表之后再修改,SQL语句如下

alter table tb_1 
add 
constraint course_Choosing
foreign key fk_course(course_id)
references st_info5(st_id);

这里需要注意的是,从表的外键关联的必须是主表的主键,且主键和外键的数据类型必须一致。例如两者都是int型或者都是char型数据。

3. 删除外键约束

一个表中不需要外键约束时,就需要从表中将其删除。外键一旦删除,就会解除主表和从表间的关联关系。

删除的语法格式:

ALTER TABLE <表名> 
DROP 
FOREIGN KEY <外键约束名>;

例:

alter table tb_1
drop
foreign key fk_course;

三. 唯一约束——UNIQUE KEY

唯一约束就是指所有记录中字段的值不能重复出现比如给’id’字段加上唯一约束之后,每条记录的id值都是唯一的,不能出现重复的情况。

唯一约束与主键约束有一个相似的地方,就是它们都能够确保列的唯一性。与主键约束不同的是,唯一约束在一个表中可以有多个,并且设置唯一约束的列是允许有空值的,虽然只能有一个空值

例如,在用户信息表中,要避免表中的用户名重名,就可以把用户名列设置为唯一约束。

1.在创建表时设置唯一约束

唯一约束可以在创建表的时候直接进行设置,通常设置在除了主键以外的其他列上,语法格式如下

<字段名> <数据类型> UNIQUE

 创建图书馆所有书本明细book_info ,设定书本bk_id为主键,书本ISBN为唯一键,SQL语句如下

create table book_info(
    bk_id int primary key,
    ISBN varchar(17) unique,
    author varchar(50),
    Publisher varchar(20)
);

mysql> desc book_info;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| bk_id | int | NO | PRI | NULL | |
| ISBN | varchar(17) | YES | UNI | NULL |       |
| author | varchar(50) | YES | | NULL | |
| Publisher | varchar(20) | YES |     | NULL |       |
+-----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
2. 在修改表时添加唯一约束

语法格式:

ALTER TABLE <数据表名> 
ADD 
CONSTRAINT <唯一约束名> UNIQUE(<列名>);

例 book_info表中author字段设置为唯一约束:

alter table book_info
add
constraint uni_author unique(author);

mysql> desc book_info;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| bk_id | int | NO | PRI | NULL | |
| ISBN | varchar(17) | YES | UNI | NULL |       |
| author | varchar(50) | YES | UNI | NULL | |
| Publisher | varchar(20) | YES |     | NULL |       |
+-----------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
3. 删除唯一约束

语法格式:

ALTER TABLE <表名> 
DROP INDEX <唯一约束名>;

例 删除book_info表中的唯一约束uni_author;

alter table book_info
drop index uni_author;

mysql> desc book_info;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| bk_id | int | NO | PRI | NULL | |
| ISBN | varchar(17) | YES | UNI | NULL |       |
| author | varchar(50) | YES | | NULL | |
| Publisher | varchar(20) | YES |     | NULL |       |
+-----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

四. 检查约束——CHECK

检查约束是用来检查数据表中字段值有效性的一种约束。

例如学生信息表中的年龄字段是不能有负数的,并且数值范围也是有限制的。如果是大学生,正常年龄一般应该在 18~30 岁之间。在设置字段的检查约束时需要根据实际情况进行设置,这样能够减少无效数据的输入

1.在创建表时设置检查约束

语法格式:

CHECK(<约束条件>)

 在persons表创建时id_p列创建check约束,约束条件为id_p列数据必须只包含大于0的整数

create table persons(
    id_p int not null,
    lastname varchar(255) not null,
    firstname varchar(255),
    address varchar(255),
    city varchar(255),
    check (id_p>0)
);

mysql> desc persons;
+-----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| id_p | int | NO | | NULL | |
| lastname | varchar(255) | NO |     | NULL |       |
| firstname | varchar(255) | YES | | NULL | |
| address | varchar(255) | YES |     | NULL |       |
| city | varchar(255) | YES | | NULL | |
+-----------+--------------+------+-----+---------+-------+
2. 在修改表时添加检查约束

语法格式:

ALTER TABLE <表名> 
ADD 
CONSTRAINT <检查约束名> CHECK(<检查约束>)

 修改persons数据表,要求city字段为“上海”:

create table persons(
    id_p int not null,
    lastname varchar(255) not null,
    firstname varchar(255),
    address varchar(255),
    city varchar(255),
    constraint chk_person check (id_p>0 and city='sandnes')
);

alter table persons 
add 
constraint chk_ct check(city='上海');

mysql> desc persons;
+-----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| id_p | int | NO | | NULL | |
| lastname | varchar(255) | NO |     | NULL |       |
| firstname | varchar(255) | YES | | NULL | |
| address | varchar(255) | YES |     | NULL |       |
| city | varchar(255) | YES | | NULL | |
+-----------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
3. 删除检查约束

语法格式:

ALTER TABLE <表名> 
DROP CONSTRAINT <检查约束名>;

 删除persons表中的check_ct约束:

alter table persons 
drop constraint chk_ct;

五. 非空约束——NOT NULL

非空约束是用来约束表中的字段不能为空。在表中某个列的定义后加上关键字NOT NULL作为限定词,来约束该列的取值不能为空。

例如,学生信息表中,如果不添加学生姓名,那么这条记录无效的,这种情况下就可以为用户名字段设置非空约束。

1.在创建表时设置非空约束

语法格式:

CREATE TABLE <表名>(
    ...
<字段名> <数据类型> NOT NULL)
...
);

例 创建学生信息表st_info, 指定st_id为主键,学生姓名不能为空:

create table st_info(
    st_id int(10) primary key,
    name varchar(20) not null,
    class varchar(10),
    gender varchar(4),
    age int(2)
);

mysql> desc st_info;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| st_id | int | NO | PRI | NULL | |
| name | varchar(20) | NO |     | NULL |       |
| class  | varchar(10) | YES  | | NULL    | |
| gender | varchar(4) | YES |     | NULL |       |
| age | int | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
2. 在修改表时添加非空约束:

如果在创建表的时候忘记了为字段设置非空约束,可以通过修改表进行非空约束的添加。

语法格式:

ALTER TABLE <表名>
CHANGE COLUMN <字段名>
<字段名> <数据类型> NOT NULL;

 修改学生信息表st_info中class字段,设置成非空约束:

mysql> alter table st_info
    -> change column class
    -> class varchar(20) not null;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc st_info;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| st_id | int | NO | PRI | NULL | |
| name | varchar(20) | NO |     | NULL |       |
| class  | varchar(20) | NO   | | NULL    | |
| gender | varchar(4) | YES |     | NULL |       |
| age | int | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
3. 删除非空约束

可以在修改语句中对非空约束进行删除。

语法格式:

ALTER TABLE <数据表名>
CHANGE COLUMN <字段名> 
<字段名> <数据类型> NULL;

 修改学生信息表st_info,将class字段的非空约束删除:

mysql> alter table st_info
    -> change column class
    -> class varchar(20) null;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc st_info;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| st_id | int | NO | PRI | NULL | |
| name | varchar(20) | NO |     | NULL |       |
| class  | varchar(20) | YES  | | NULL    | |
| gender | varchar(4) | YES |     | NULL |       |
| age | int | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

六. 默认值约束——DEFAULT

默认值约束是当数据表中某个字段不输入值的时候,自动为其添加一个已经设置好的值。

例如,在注册学生信息时,如果不输入学生的性别,那么会默认设置一个性别或者输入一个“未知”。

默认值约束一般是用在已经设置了非空约束的列,这样能够防止数据表在录入数据时出现错误

1.在创建表时设置默认值约束

语法格式:

CREATE TABLE <表名>(
    ...
<字段名> <数据类型> DEFAULT <默认值>
    ...
);

 创建学生信息表st_info,设置年龄age默认值为18:

create table st_info(
    st_id int(10) primary key,
    name varchar(20) not null,
    class varchar(10),
    gender varchar(4),
    age int(2) default 18
);

mysql> desc st_info;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| st_id | int | NO | PRI | NULL | |
| name | varchar(20) | NO |     | NULL |       |
| class  | varchar(10) | YES  | | NULL    | |
| gender | varchar(4) | YES |     | NULL |       |
| age | int | YES | | 18      | |
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.01 sec)
2. 在修改表时添加默认值约束

语法格式:

ALTER TABLE <数据表名>
CHANGE COLUMN <字段名> 
<字段名> <数据类型> DEFAULT <默认值>;

 修改学生信息表st_info,将性别设置默认值为保密”:

mysql> alter table st_info
    -> change column gender
    -> gender varchar(4) default '保密';
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc st_info;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| st_id | int | NO | PRI | NULL | |
| name | varchar(20) | NO |     | NULL |       |
| class  | varchar(10) | YES  | | NULL    | |
| gender | varchar(4) | YES |     | 保密 |       |
| age | int | YES | | 18      | |
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

需要注意的是,这里的gender字段为字符串型数据,需要加上引号

3. 删除默认值

删除默认值即为将默认值设置为空(null)

语法格式:

ALTER TABLE <数据表名>
CHANGE COLUMN <字段名> 
<字段名> <数据类型> DEFAULT NULL;

 修改学生信息表st_info, 将gender性别字段的默认值删除掉:

mysql> alter table st_info
    -> change column gender
    -> gender varchar(4) default null;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc st_info;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| st_id | int | NO | PRI | NULL | |
| name | varchar(20) | NO |     | NULL |       |
| class  | varchar(10) | YES  | | NULL    | |
| gender | varchar(4) | YES |     | NULL |       |
| age | int | YES | | 18      | |
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

以上6个约束都很重要,并且有的可以联合使用,并不是都是单独使用的。作为基础知识点,会让你的数据库更加规范,从而提高工作效率

原文地址:https://blog.csdn.net/qq_57570052/article/details/132153848

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任

如若转载,请注明出处:http://www.7code.cn/show_44378.html

如若内容造成侵权/违法违规/事实不符,请联系代码007邮箱suwngjj01@126.com进行投诉反馈,一经查实,立即删除!

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注