主键约束
? 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 changedmysql> show columns from t3;+----------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+----------+-------------+------+-----+---------+-------+| username | varchar(20) | YES | UNI | NULL | |+----------+-------------+------+-----+---------+-------+1 row in setmysql> insert into t3 values('zhang');Query OK, 1 row affectedmysql> insert into t3 values('zhang');1062 - Duplicate entry 'zhang' for key 1? 添加的空的话会不会唯一呢？
??
mysql> insert into t3 values();Query OK, 1 row affectedmysql> insert into t3 values();Query OK, 1 row affectedmysql> insert into t3 values();Query OK, 1 row affectedmysql> 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 affectedmysql> select username from t5;+----------+| username |+----------+| zhangsan |+----------+1 row in set
?非空约束
? ?
mysql> create table t6( -> username varchar(20) not null);Database changedmysql> 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;
?
? ?
??