mysql 基础-约束

主键约束

  1 在创建主键时候如果有auto_increment关键字，那么必须设置为主键，否则报错

mysql> create table t2(
    -> id smallInt unsigned auto_increment,
    -> username varchar(30) not null
    -> );
1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key

    设置成主键后

mysql> create table t2(
    -> id smallInt unsigned auto_increment primary key,
    -> username varchar(30)
    -> );
Database changed

   显示字段的详细信息

mysql> show columns from t2;
+----------+----------------------+------+-----+---------+----------------+
| Field    | Type                 | Null | Key | Default | Extra          |
+----------+----------------------+------+-----+---------+----------------+
| id       | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(30)          | YES  |     | NULL    |                |
+----------+----------------------+------+-----+---------+----------------+
2 rows in set

 唯一约束

mysql> create table t3(
    -> username varchar(20) unique key);
Database changed
mysql> show columns from t3;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(20) | YES  | UNI | NULL    |       |
+----------+-------------+------+-----+---------+-------+
1 row in set

mysql> insert into t3 values('zhang');
Query OK, 1 row affected

mysql> insert into t3 values('zhang');
1062 - Duplicate entry 'zhang' for key 1

  添加的空的话会不会唯一呢？

mysql> insert into t3 values();
Query OK, 1 row affected

mysql> insert into t3 values();
Query OK, 1 row affected

mysql> insert into t3 values();
Query OK, 1 row affected

mysql> select username from t3;
+----------+
| username |
+----------+
| NULL     |
| NULL     |
| NULL     |
| NULL     |
| zhang    |
+----------+

 默认约束，没有值的情况下的约束

mysql> create table t5(
    -> username varchar(30) default 'zhangsan'
    -> );
mysql> insert into t5 values();
Query OK, 1 row affected
mysql> select username from t5;
+----------+
| username |
+----------+
| zhangsan |
+----------+
1 row in set

 非空约束

mysql> create table t6(
    -> username varchar(20) not null);
Database changed
mysql> insert into t6 values();
1364 - Field 'username' doesn't have a default value

  外键约束 PROEIGN KEY

  1保持数据一致性，实现一对一或者一对多的关系

  2要求 父表子表必须使用相同的存储引擎 innoDB ,default -storage-engine=INNODB 

  3禁止使用临时表，必须据说相似的数据类型

  4 外键列跟参照列必须创建索引

mysql> create table dept(
    -> id smallint auto_increment primary key,
    -> deptName varchar(20));

mysql> create table user(
    -> uid smallint auto_increment primary key,
    -> username varchar(20),
    -> deptId smallint,
    -> FOREIGN KEY(deptId) REFERENCES dept(id));
Database changed

   查索引命令用show index from user;