【笔记七】：数据约束

最新推荐文章于 2022-08-25 12:46:15 发布

林中徘徊

最新推荐文章于 2022-08-25 12:46:15 发布

阅读量245

点赞数

分类专栏： mysql 文章标签： mysql

本文链接：https://blog.csdn.net/u010177891/article/details/105235094

版权

mysql 专栏收录该内容

8 篇文章 2 订阅

订阅专栏

1，主键约束

一般情况下每张表中都需要创建一列作为主键列

特性：

1，非空

2，唯一，即不允许重复

3，被引用可以作为其他表的外键

三种创建方式

第一种：直接在某一列后面加关键字：“PRIMARY KEY”

create table stu(
    sid     int(6) primary key,
    sname   varchar(20),
    age     int(3),
    gender  varchar(10)
);

第二种在：专门去指定某一列为主键列，PRIMARY KEY(<列名>)

create table stu(
	sid     int(6),
	sname   varchar(20),
	age     int(3),
	gender  varchar(10),

	primary key(sid)
);

第三种：alter table stu add primary key(sid);

MariaDB [mydb]> alter table stu add primary key(sid);
Query OK, 0 rows affected (0.088 sec)
Records: 0  Duplicates: 0  Warnings: 0

2，主键自增长约束

满足是主键且是整数可以进行自增操作。

方式一：在主键后边添加“auto_increment”

create table stu(
   sid     int(6) primary key auto_increment,
   sname   varchar(20),
   age     int(3),
   gender  varchar(10)
);

MariaDB [mydb]> insert into stu(sname) values("张三");
Query OK, 1 row affected (0.007 sec)

MariaDB [mydb]> insert into stu values(0,"李四",16,"一班");
Query OK, 1 row affected (0.012 sec)

MariaDB [mydb]> select * from stu;
+-----+--------+------+--------+
| sid | sname  | age  | gender |
+-----+--------+------+--------+
|   1 | 张三   | NULL | NULL   |
|   2 | 李四   |   16 | 一班   |
+-----+--------+------+--------+
2 rows in set (0.001 sec)

方式二：alter table <表名> change <主键> <主键> <主键类型> auto_increment;

MariaDB [mydb]> alter table stu change sid sid int auto_increment;
Query OK, 0 rows affected (0.077 sec)              
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [mydb]> desc stu;
+--------+-------------+------+-----+---------+----------------+
| Field  | Type        | Null | Key | Default | Extra          |
+--------+-------------+------+-----+---------+----------------+
| sid    | int(11)     | NO   | PRI | NULL    | auto_increment |
| sname  | varchar(20) | YES  |     | NULL    |                |
| age    | int(3)      | YES  |     | NULL    |                |
| gender | varchar(10) | YES  |     | NULL    |                |
+--------+-------------+------+-----+---------+----------------+
4 rows in set (0.005 sec)

3，非空约束

当某些列不能设置为NULL值，所以可以对列添加非空约束。关键字“not null”

create table stu(
   sid     int(6),
   sname   varchar(20) not null,
   age     int(3),
   gender  varchar(10)
);

MariaDB [mydb]> create table stu(
    ->    sid     int(6),
    ->    sname   varchar(20) not null,
    ->    age     int(3),
    ->    gender  varchar(10)
    -> );
Query OK, 0 rows affected (0.046 sec)

MariaDB [mydb]> desc stu;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| sid    | int(6)      | YES  |     | NULL    |       |
| sname  | varchar(20) | NO   |     | NULL    |       |
| age    | int(3)      | YES  |     | NULL    |       |
| gender | varchar(10) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
MariaDB [mydb]> insert into stu values(0,null,1,"5");
ERROR 1048 (23000): Column 'sname' cannot be null

4，唯一约束

数据库中某些列不能设置重复的值，不重复但是可以为空。关键字“unique”

create table stu(
   sid     int(6),
   sname   varchar(20) unique,
   age     int(3),
   gender  varchar(10)
);

MariaDB [mydb]> create table stu(
    ->    sid     int(6),
    ->    sname   varchar(20) unique,
    ->    age     int(3),
    ->    gender  varchar(10)
    -> );
Query OK, 0 rows affected (0.046 sec)

MariaDB [mydb]> desc stu;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| sid    | int(6)      | YES  |     | NULL    |       |
| sname  | varchar(20) | YES  | UNI | NULL    |       |
| age    | int(3)      | YES  |     | NULL    |       |
| gender | varchar(10) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.005 sec)

MariaDB [mydb]> insert into stu values( 0, "张三", 1, "5" );
Query OK, 1 row affected (0.007 sec)

MariaDB [mydb]> insert into stu values( 1, "张三", 2, "6" );
ERROR 1062 (23000): Duplicate entry '张三' for key 'sname'
MariaDB [mydb]> insert into stu values( 3, null, 3, "7" );
Query OK, 1 row affected (0.008 sec)

MariaDB [mydb]> select * from stu;
+------+--------+------+--------+
| sid  | sname  | age  | gender |
+------+--------+------+--------+
|    0 | 张三   |    1 | 5      |
|    3 | NULL   |    3 | 7      |
+------+--------+------+--------+
2 rows in set (0.001 sec)

5，默认值约束

如果不给值就使用定义的默认值。关键字“default” default <初始值> 注意：如果值是字符串和日期需要加单引号

create table stu(
   sid     int(6),
   sname   varchar(20),
   age     int(3),
   gender  varchar(10) default "1"
);

MariaDB [mydb]> create table stu(
    ->    sid     int(6),
    ->    sname   varchar(20),
    ->    age     int(3),
    ->    gender  varchar(10) default "1"
    -> );
Query OK, 0 rows affected (0.033 sec)

MariaDB [mydb]> desc stu;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| sid    | int(6)      | YES  |     | NULL    |       |
| sname  | varchar(20) | YES  |     | NULL    |       |
| age    | int(3)      | YES  |     | NULL    |       |
| gender | varchar(10) | YES  |     | 1       |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.004 sec)

MariaDB [mydb]> insert into stu(sid,sname,age) values(1,"张三",13);
Query OK, 1 row affected (0.008 sec)

MariaDB [mydb]> insert into stu values(2,"李四",15, "6");
Query OK, 1 row affected (0.007 sec)

MariaDB [mydb]> select * from stu;
+------+--------+------+--------+
| sid  | sname  | age  | gender |
+------+--------+------+--------+
|    1 | 张三   |   13 | 1      |
|    2 | 李四   |   15 | 6      |
+------+--------+------+--------+
2 rows in set (0.002 sec)

6，外键约束

建立表与表之间的关系，外键的本质就是保存别的表中已存在的主键值。

constraint <自定义约束名称> foreign key(<外键名称>) references <关联的表名>(关联表的主键列名)

方式一：创建表时指定

create table stu(
   sid       int(6),
   sname     varchar(20),
   age       int(3),
   gender    varchar(10),
   teacherid int(6),

   constraint fk_tid foreign key(teacherid) references teacher(tid)
);

MariaDB [mydb]> create table teacher(
    ->    tid     int(6) primary key,
    ->    tname   varchar(20)
    -> );
Query OK, 0 rows affected (0.028 sec)

MariaDB [mydb]> create table stu(
    ->    sid       int(6),
    ->    sname     varchar(20),
    ->    age       int(3),
    ->    gender    varchar(10),
    ->    teacherid int(6),
    -> 
    ->    constraint fk_tid foreign key(teacherid) references teacher(tid)
    -> );
Query OK, 0 rows affected (0.101 sec)
MariaDB [mydb]> desc stu;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| sid       | int(6)      | YES  |     | NULL    |       |
| sname     | varchar(20) | YES  |     | NULL    |       |
| age       | int(3)      | YES  |     | NULL    |       |
| gender    | varchar(10) | YES  |     | NULL    |       |
| teacherid | int(6)      | YES  | MUL | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
5 rows in set (0.004 sec)

MariaDB [mydb]> desc teacher;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| tid   | int(6)      | NO   | PRI | NULL    |       |
| tname | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.003 sec)
MariaDB [mydb]> insert into teacher values(1,"李老师");
Query OK, 1 row affected (0.015 sec)

MariaDB [mydb]> insert into stu values(4, "张三", 16, "5", 3);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`mydb`.`stu`, CONSTRAINT `fk_tid` FOREIGN KEY (`teacherid`) REFERENCES `teacher` (`tid`))
MariaDB [mydb]> insert into stu values(4, "张三", 16, "5", 1);
Query OK, 1 row affected (0.007 sec)

方式二：修改表时添加外键约束

 alter table <表名> add constraint <自定义约束名称> foreign key(<外键名称>) references <关联的表名>(关联表的主键列名)

MariaDB [mydb]> alter table stu add constraint fk_tid foreign key(teacherid) references teacher(tid);
Query OK, 0 rows affected (0.091 sec)              
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [mydb]> insert into stu values(4, "张三", 16, "5", 100);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`mydb`.`stu`, CONSTRAINT `fk_tid` FOREIGN KEY (`teacherid`) REFERENCES `teacher` (`tid`))
MariaDB [mydb]> insert into stu values(4, "张三", 16, "5", 1);
Query OK, 1 row affected (0.007 sec)

方式三：修改表时删除外键约束

alter table <表名> drop foreign key <外键名称>;

MariaDB [mydb]> alter table stu drop foreign key fk_tid;
Query OK, 0 rows affected (0.012 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [mydb]> insert into stu values(4, "张三", 16, "5", 100);
Query OK, 1 row affected (0.007 sec)

林中徘徊

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
【笔记七】：数据约束

目录1，主键约束2，主键自增长约束3，非空约束4，唯一约束5，默认值约束6，外键约束1，主键约束一般情况下每张表中都需要创建一列作为主键列特性：1，非空2，唯一，即不允许重复3，被引用可以作为其他表的外键三种创建方式第一种：直接在某一列后面加关键字：“PRIMARY KEY”create table stu( sid ...
复制链接

扫一扫