mysql 键_mysql --键表约束

--mysql 键表约束，sql

--主键约束；

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

有且只有一个

create table user (

id int primary key,

name varchar(20)

);

mysql> describe user;

+-------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+-------------+------+-----+---------+-------+

| id | int | NO | PRI | NULL | |

| name | varchar(20) | YES | | NULL | |

+-------+-------------+------+-----+---------+-------+

2 rows in set (0.01 sec)

mysql> insert into user values(1,'zhangsan');

Query OK, 1 row affected (0.00 sec)

mysql> insert into user values(1,'zhangsan');

ERROR 1062 (23000): Duplicate entry '1' for key 'user.PRIMARY'

mysql>

mysql> insert into user values(1,'zhangsan');

ERROR 1062 (23000): Duplicate entry '1' for key 'user.PRIMARY'

mysql> insert into user values(2,'zhangsan');

Query OK, 1 row affected (0.00 sec)

mysql> select * from user;

+----+----------+

| id | name |

+----+----------+

| 1 | zhangsan |

| 2 | zhangsan |

+----+----------+

2 rows in set (0.00 sec)

mysql>

mysql> insert into user values(null,'zhangsan');

ERROR 1048 (23000): Column 'id' cannot be null

mysql>

--联合主键；

--只要联合主键值加起来不重复就可以,也不可以为空；

mysql> create table user2(

-> id int,

-> name varchar(20),

-> password varchar(20),

-> primary key(id,name)

-> );

Query OK, 0 rows affected (0.01 sec)

mysql> select * from user2;

+----+----------+----------+

| id | name | password |

+----+----------+----------+

| 1 | lisi | 234 |

| 1 | zhangsan | 123 |

| 2 | zhangsan | 123 |

+----+----------+----------+

3 rows in set (0.00 sec)

--自增约束

mysql> create table user3(

-> id int primary key auto_increment,

-> name varchar(20)

-> );

Query OK, 0 rows affected (0.01 sec)

mysql> insert into user3 (name) values('zhangsan');

Query OK, 1 row affected (0.01 sec)

mysql> select * from user3;

+----+----------+

| id | name |

+----+----------+

| 1 | zhangsan |

+----+----------+

1 row in set (0.00 sec)

mysql> insert into user3 (name) values('zhangsan');

Query OK, 1 row affected (0.00 sec)

mysql> select * from user3;

+----+----------+

| id | name |

+----+----------+

| 1 | zhangsan |

| 2 | zhangsan |

+----+----------+

2 rows in set (0.00 sec)

--> 就是自我增长

----> 在上面的基础上添加主键约束：通过alter

mysql> alter table user4 add primary key(id);

Query OK, 0 rows affected (0.02 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc user4;

+-------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+-------------+------+-----+---------+-------+

| id | int | NO | PRI | NULL | |

| name | varchar(20) | YES | | NULL | |

+-------+-------------+------+-----+---------+-------+

2 rows in set (0.01 sec)

mysql>

--如何删除

alter table user4 drop primary key;

uery OK, 0 rows affected (0.02 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc user4;

+-------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+-------------+------+-----+---------+-------+

| id | int | NO | | NULL | |

| name | varchar(20) | YES | | NULL | |

+-------+-------------+------+-----+---------+-------+

2 rows in set (0.01 sec)

mysql>

使用modify 修改字段，添加约束；

mysql> alter table user4 modify id int primary key;

Query OK, 0 rows affected (0.01 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc user4;

+-------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+-------------+------+-----+---------+-------+

| id | int | NO | PRI | NULL | |

| name | varchar(20) | YES | | NULL | |

+-------+-------------+------+-----+---------+-------+

2 rows in set (0.01 sec)

mysql>

--唯一约束

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

mysql> alter table user5 add unique(name);

Query OK, 0 rows affected (0.01 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc user5;

+-------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+-------------+------+-----+---------+-------+

| id | int | YES | | NULL | |

| name | varchar(20) | YES | UNI | NULL | |

+-------+-------------+------+-----+---------+-------+

2 rows in set (0.00 sec)

mysql>

mysql> insert into user5 values(1,"zhangsan");

Query OK, 1 row affected (0.00 sec)

mysql> insert into user5 values(1,"zhangsan");

ERROR 1062 (23000): Duplicate entry 'zhangsan' for key 'user5.name'

mysql> insert into user5 values(1,"lisi");

Query OK, 1 row affected (0.00 sec)

mysql>

create table user6(

id int,

name varchar(20),

unique(name)

);

mysql> desc user6;

+-------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+-------------+------+-----+---------+-------+

| id | int | YES | | NULL | |

| name | varchar(20) | YES | UNI | NULL | |

+-------+-------------+------+-----+---------+-------+

2 rows in set (0.00 sec)

create table user8(

id int,

name varchar(20),

unique (id,name)

);

mysql> desc user8;

+-------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+-------------+------+-----+---------+-------+

| id | int | YES | MUL | NULL | |

| name | varchar(20) | YES | | NULL | |

+-------+-------------+------+-----+---------+-------+

2 rows in set (0.01 sec)

mysql>

mysql> insert into user8 values(1,"zhangsan");

Query OK, 1 row affected (0.00 sec)

mysql> insert into user8 values(1,"zhangsan");

ERROR 1062 (23000): Duplicate entry '1-zhangsan' for key 'user8.id'

mysql> insert into user8 values(2,"zhangsan");

Query OK, 1 row affected (0.00 sec)

mysql> select * from user8;

+------+----------+

| id | name |

+------+----------+

| 1 | zhangsan |

| 2 | zhangsan |

+------+----------+

2 rows in set (0.00 sec)

mysql>

----如何删除唯一约束；

alter tabble user7 drop index name;

mysql> alter table user7 drop index name;

Query OK, 0 rows affected (0.01 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc user7;

+-------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+-------------+------+-----+---------+-------+

| id | int | YES | | NULL | |

| name | varchar(20) | YES | | NULL | |

+-------+-------------+------+-----+---------+-------+

2 rows in set (0.00 sec)

mysql>

---通过modify 添加唯一约束；

alter table user7 modify name varchar(20) unique;

mysql> alter table user7 modify name varchar(20) unique;

Query OK, 0 rows affected (0.01 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc user7;

+-------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+-------------+------+-----+---------+-------+

| id | int | YES | | NULL | |

| name | varchar(20) | YES | UNI | NULL | |

+-------+-------------+------+-----+---------+-------+

2 rows in set (0.00 sec)

mysql>

---总结

--1 建表的时候添加约束；

--2 可以使用alter .....add.....

--3 alter .....modify....

--4 s删除。alter 。。。。。drop...

--外键约束；

--唯一约束；

--非空约束；

--默认约束；