当前位置:Gxlcms > 数据库问题 > mysql建表约束

mysql建表约束

时间:2021-07-01 10:21:17 帮助过:5人阅读

mysql建表约束

  • 主键约束

他能够唯一确定一张表中的一条记录,也就是我们通过给某个字段添加约束,就可以使的该字段不重复且不为空

  1. <code>create table user(
  2. id int primary key,
  3. name varchar(20)
  4. );
  5. </code>

输入 describe user;

  1. <code>+-------+-------------+------+-----+---------+-------+
  2. | Field | Type | Null | Key | Default | Extra |
  3. +-------+-------------+------+-----+---------+-------+
  4. | id | int | NO | PRI | NULL | |
  5. | name | varchar(20) | YES | | NULL | |
  6. +-------+-------------+------+-----+---------+-------+
  7. 2 rows in set (0.00 sec)
  8. </code>

这里的key值下的PRI即为主键约束

我们可以插入一个记录

insert into user values(1,‘张三‘);

再插入一次相同的记录

insert into user values(1,‘张三‘);

报错-------,是因为key值为1已经固定,不能再次插入key为1的记录,也不能为空

输入select * from user;

  1. <code>+----+------+
  2. | id | name |
  3. +----+------+
  4. | 1 | 张三 |
  5. +----+------+
  6. </code>

还有一种情况,成为联合主键

  1. <code>> create table user2(
  2. -> id int,
  3. -> name varchar(20),
  4. -> password varchar(20),
  5. -> primary key(id,name) //表示id 和name 两个有一个不重复就可以
  6. -> );
  7. </code>
  • 自增约束

自动添加主键约束

  1. <code>create table user3(
  2. -> id int primary key auto_increment,
  3. -> name varchar(20)
  4. -> );
  5. </code>

添加记录:insert into user3 (name) values(‘张三‘);

  1. <code>mysql> select * from user3;
  2. +----+------+
  3. | id | name |
  4. +----+------+
  5. | 1 | 张三 |
  6. +----+------+
  7. </code>

再次添加记录:insert into user3 (name) values(‘张三‘);

  1. <code>mysql> select * from user3;
  2. +----+------+
  3. | id | name |
  4. +----+------+
  5. | 1 | 张三 |
  6. | 2 | 张三 |
  7. +----+------+
  8. </code>

如果创建表时忘记添加主键约束该怎么添加主键?

  1. <code>create table user4(
  2. -> id int,
  3. -> name varchar(20)
  4. -> );
  5. </code>

decs user4

  1. <code>mysql> desc user4
  2. -> ;
  3. +-------+-------------+------+-----+---------+-------+
  4. | Field | Type | Null | Key | Default | Extra |
  5. +-------+-------------+------+-----+---------+-------+
  6. | id | int | YES | | NULL | |
  7. | name | varchar(20) | YES | | NULL | |
  8. +-------+-------------+------+-----+---------+-------+
  9. </code>

我们可以 alter table user4 add primary key(id);或者输入alter table user4 modify id int primary key;

  1. <code>mysql> desc user4
  2. -> ;
  3. +-------+-------------+------+-----+---------+-------+
  4. | Field | Type | Null | Key | Default | Extra |
  5. +-------+-------------+------+-----+---------+-------+
  6. | id | int | NO | PRI | NULL | |
  7. | name | varchar(20) | YES | | NULL | |
  8. +-------+-------------+------+-----+---------+-------+
  9. </code>

同理,可以添加主键也可以删除主键:

alter table user4 drop primary key;

  • 唯一约束

约束修饰的字段的值不可以重复

  1. <code>create table user5(
  2. -> id int,
  3. -> name varchar(20)
  4. -> );
  5. </code>
  1. <code>desc user5
  2. -> ;
  3. +-------+-------------+------+-----+---------+-------+
  4. | Field | Type | Null | Key | Default | Extra |
  5. +-------+-------------+------+-----+---------+-------+
  6. | id | int | YES | | NULL | |
  7. | name | varchar(20) | YES | | NULL | |
  8. +-------+-------------+------+-----+---------+-------+
  9. 2 rows in set (0.00 sec)
  10. </code>

输入:alter table user5 add unique(name);

  1. <code>mysql> desc user5
  2. -> ;
  3. +-------+-------------+------+-----+---------+-------+
  4. | Field | Type | Null | Key | Default | Extra |
  5. +-------+-------------+------+-----+---------+-------+
  6. | id | int | YES | | NULL | |
  7. | name | varchar(20) | YES | UNI | NULL | |
  8. +-------+-------------+------+-----+---------+-------+
  9. </code>

添加记录:insert into user values(1,‘张三‘);

  1. <code>mysql> select * from user5;
  2. +----+------+
  3. | id | name |
  4. +----+------+
  5. | 1 | 张三 |
  6. +----+------+
  7. </code>

再次添加记录:insert into user values(1,‘张三‘);

报错,重复

把名字改成李四就可以(insert into user values(1,‘李四‘);

也可以在创建的时候直接添加约束

  1. <code>create table user6(
  2. id int,
  3. name varchar(20)
  4. unique(name)
  5. );
  6. </code>

输出为

  1. <code>mysql> desc user6;
  2. +-------+-------------+------+-----+---------+-------+
  3. | Field | Type | Null | Key | Default | Extra |
  4. +-------+-------------+------+-----+---------+-------+
  5. | id | int | YES | | NULL | |
  6. | name | varchar(20) | YES | UNI | NULL | |
  7. +-------+-------------+------+-----+---------+-------+
  8. 2 rows in set (0.00 sec)
  9. </code>

此外,unique还可以改变位置

  1. <code> create table user7(
  2. -> id int,
  3. -> name varchar(20),unique
  4. -> );
  5. mysql> desc user7
  6. -> ;
  7. +-------+-------------+------+-----+---------+-------+
  8. | Field | Type | Null | Key | Default | Extra |
  9. +-------+-------------+------+-----+---------+-------+
  10. | name | varchar(20) | YES | UNI | NULL | |
  11. +-------+-------------+------+-----+---------+-------+
  12. </code>
  1. <code>mysql> create table user8(
  2. -> id int,
  3. -> name varchar(20),
  4. -> unique(id,name)//用法同联合主键,有一个不重复的就可以
  5. -> );
  6. Query OK, 0 rows affected (0.03 sec)
  7. mysql> desc user8;
  8. +-------+-------------+------+-----+---------+-------+
  9. | Field | Type | Null | Key | Default | Extra |
  10. +-------+-------------+------+-----+---------+-------+
  11. | id | int | YES | MUL | NULL | |
  12. | name | varchar(20) | YES | | NULL | |
  13. +-------+-------------+------+-----+---------+-------+
  14. </code>
  • 如何删除唯一约束?

alter table user7 drop index name;

  1. <code>mysql> desc user7;
  2. +-------+-------------+------+-----+---------+-------+
  3. | Field | Type | Null | Key | Default | Extra |
  4. +-------+-------------+------+-----+---------+-------+
  5. | name | varchar(20) | YES | | NULL | |
  6. +-------+-------------+------+-----+---------+-------+
  7. 1 row in set (0.00 sec)
  8. </code>
  • 如何添加唯一约束?

alter table user7 modify name varchar(20) unique;

  • 非空约束

修饰的字段不能为空 NULL

  1. <code>mysql> create table user9(
  2. -> id int,
  3. -> name varchar(20) not null
  4. -> );
  5. </code>

输出:

  1. <code>mysql> desc user9
  2. -> ;
  3. +-------+-------------+------+-----+---------+-------+
  4. | Field | Type | Null | Key | Default | Extra |
  5. +-------+-------------+------+-----+---------+-------+
  6. | id | int | YES | | NULL | |
  7. | name | varchar(20) | NO | | NULL | |
  8. +-------+-------------+------+-----+---------+-------+
  9. </code>

可以看到 name值的null显示不能为空,即添加记录时name的null值不能为空,即

输入 insert into user9 values(1);会出错

输入insert into user9 values(1,’张三‘)就不会报错

  • 默认约束

当插入字段值的时候,没有传值,就会使用默认值

  1. <code>mysql> create table user10(
  2. -> id int,
  3. -> name varchar(20),
  4. -> age int default 10
  5. -> );
  6. Query OK, 0 rows affected (0.04 sec)
  7. mysql> desc user10;
  8. +-------+-------------+------+-----+---------+-------+
  9. | Field | Type | Null | Key | Default | Extra |
  10. +-------+-------------+------+-----+---------+-------+
  11. | id | int | YES | | NULL | |
  12. | name | varchar(20) | YES | | NULL | |
  13. | age | int | YES | | 10 | |
  14. +-------+-------------+------+-----+---------+-------+
  15. 3 rows in set (0.00 sec)
  16. </code>
  1. <code>mysql> insert into user10 (id,name) values(1,‘zhangsan‘);//不传年龄就默认为10
  2. Query OK, 1 row affected (0.01 sec)
  3. mysql> select * from user10;
  4. +------+----------+------+
  5. | id | name | age |
  6. +------+----------+------+
  7. | 1 | zhangsan | 10 |
  8. +------+----------+------+
  9. </code>
  • 外键约束

涉及两个表父表(主表)、子表(副表)

例如,有一个班级表(主表)

  1. <code>create table classes(
  2. -> id int primary key,
  3. -> name varchar(20)
  4. -> );
  5. </code>

还有一个学生表(副表)

  1. <code> create table students(
  2. -> id int primary key,
  3. -> name varchar(20),
  4. -> class_id int,
  5. -> foreign key(class_id) references classes(id)//指明副表里的id来自哪个表
  6. -> );
  7. </code>

分别desc

  1. <code> desc classes;
  2. +-------+-------------+------+-----+---------+-------+
  3. | Field | Type | Null | Key | Default | Extra |
  4. +-------+-------------+------+-----+---------+-------+
  5. | id | int | NO | PRI | NULL | |
  6. | name | varchar(20) | YES | | NULL | |
  7. +-------+-------------+------+-----+---------+-------+
  8. </code>
  1. <code>desc students;
  2. +----------+-------------+------+-----+---------+-------+
  3. | Field | Type | Null | Key | Default | Extra |
  4. +----------+-------------+------+-----+---------+-------+
  5. | id | int | NO | PRI | NULL | |
  6. | name | varchar(20) | YES | | NULL | |
  7. | class_id | int | YES | MUL | NULL | |
  8. +----------+-------------+------+-----+---------+-------+
  9. </code>

首先往主表里插入数据:

insert into classes values(1,‘一班‘);

insert into classes values(2,‘二班‘);

insert into classes values(3,‘三班‘);

insert into classes values(4,‘四班‘);

查看数据------------select * from classes;

  1. <code> select * from classes;
  2. +----+------+
  3. | id | name |
  4. +----+------+
  5. | 1 | 一班 |
  6. | 2 | 二班 |
  7. | 3 | 三班 |
  8. | 4 | 四班 |
  9. +----+------+
  10. </code>

之后往副表里插入数据:

insert into students values(1001,‘甲‘,1);

insert into students values(1002,‘乙‘,2);

insert into students values(1003,‘丙‘,3);

insert into students values(1004,‘丁‘,4);

没有报错,但是 若insert into students values(1005,‘小明‘,5);就会报错,因为

主表中没有的数据值,在副表中是不可以使用的。
主表中的信息被副表引用,是不可以被删除的

总结自 [code158编程俱乐部]http://www.code158.com mysql编程系列教程

mysql建表约束

标签:建表   约束   联合   password   通过   查看   创建   alter   自增   

人气教程排行