创建数据表主键外键

1、创建数据表
按行和列的方式存储，每一行唯一一条记录，每一列代表记录中的某个字段或者是域。
格式：表的名称不区分大小写，不能使用SQL关键字；存在多列使用逗号分隔。

create table (字段1，数据类型 [列约束条件]，字段2，数据类型 [列约束条件]，字段3，数据类型 [列约束条件]，........[表级别约束条件]);

测试：表名test01

| 字段名 | 数据类型 |
| name | varchar (30) |
| id | int (11) |

（1）主键约束
单字段

mysql> create table test02    -> (id int(11) primary key,    -> name varchar(30));Query OK, 0 rows affected (0.08 sec)

mysql> create table test03    -> (id int(11),    -> name varchar(30),    -> primary key (id));Query OK, 0 rows affected (0.10 sec)

mysql> desc test02;+-------+-------------+------+-----+---------+-------+| Field | Type        | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id    | int(11)     | NO   | PRI | NULL    |       || name  | varchar(30) | YES  |     | NULL    |       |+-------+-------------+------+-----+---------+-------+2 rows in set (0.00 sec)mysql> desc test03;+-------+-------------+------+-----+---------+-------+| Field | Type        | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id    | int(11)     | NO   | PRI | 0       |       || name  | varchar(30) | YES  |     | NULL    |       |+-------+-------------+------+-----+---------+-------+2 rows in set (0.01 sec)

列的会隐含一个rowid字段
表的会明确要求id是可识别的标志

多字段

mysql> create table test05 (id int(11), name varchar(30),primary key(id,name)); Query OK, 0 rows affected (0.11 sec)

删除主键约束

mysql> alter table test0004 drop primary key;  Query OK, 0 rows affected (0.08 sec) Records: 0  Duplicates: 0  Warnings: 0  mysql> show create table test0004\G;*************************** 1. row *************************** Table: test0004 Create Table: CREATE TABLE 'test0004` (   `username` varchar(10) NOT NULL,`pid` smallint(5) unsigned DEFAULT NULL,`id` smallint(5) unsigned NOT NULL DEFAULT '0', `age` tinyint(3) unsigned NOT NULL,UNIQUE KEY `username` (`username`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) 

mysql> desc test0004; +----------+----------------------+------+-----+---------+-------+ | Field    | Type                 | Null | Key | Default | Extra | +----------+----------------------+------+-----+---------+-------+ |username | varchar(10)          | NO   | PRI | NULL    |       | | pid      | smallint(5) unsigned | YES  |     | NULL    |       | | id       | smallint(5) unsigned | NO   |     | 0       |       | | age      | tinyint(3) unsigned  | NO   |     | NULL    |       | +----------+----------------------+------+-----+---------+-------+ 4 rows in set (0.00 sec)

怎么在已有的表中添加主键呢？
举个例子：
创建一个书记表，后在增加一个字段

mysql> create table test0004( username varchar(10) not null, pid smallint unsigned);Query OK, 0 rows affected (0.13 sec)mysql> desc test0004;+----------+----------------------+------+-----+---------+-------+| Field    | Type                 | Null | Key | Default | Extra |+----------+----------------------+------+-----+---------+-------+| username | varchar(10)          | NO   |     | NULL    |       || pid      | smallint(5) unsigned | YES  |     | NULL    |       |+----------+----------------------+------+-----+---------+-------+2 rows in set (0.00 sec)mysql> alter table test0004 add id smallint  unsigned;Query OK, 0 rows affected (0.10 sec)Records: 0  Duplicates: 0  Warnings: 0

增加一个主键然后验证

mysql> alter table test0004 add constraint PK_test0004_id primary key (id);Query OK, 0 rows affected (0.10 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> desc test0004;+----------+----------------------+------+-----+---------+-------+| Field    | Type                 | Null | Key | Default | Extra |+----------+----------------------+------+-----+---------+-------+| username | varchar(10)          | NO   |     | NULL    |       || pid      | smallint(5) unsigned | YES  |     | NULL    |       || id       | smallint(5) unsigned | NO   | PRI | 0       |       |+----------+----------------------+------+-----+---------+-------+3 rows in set (0.00 sec)mysql> show create table test0004\G;*************************** 1. row ***************************       Table: test0004Create Table: CREATE TABLE `test0004` (  `username` varchar(10) NOT NULL,  `pid` smallint(5) unsigned DEFAULT NULL,  `id` smallint(5) unsigned NOT NULL DEFAULT '0',  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec)

那我们添加一个唯一约束来看一下

mysql> alter table test0004 add unique (username);Query OK, 0 rows affected (0.04 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> show create table test0004\G;*************************** 1. row ***************************       Table: test0004Create Table: CREATE TABLE `test0004` (  `username` varchar(10) NOT NULL,  `pid` smallint(5) unsigned DEFAULT NULL,  `id` smallint(5) unsigned NOT NULL DEFAULT '0',  PRIMARY KEY (`id`),  UNIQUE KEY `username` (`username`)) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec)

接下来我们给他添加一个字段，并修改和删除默认值操作

mysql> alter table test0004 add age tinyint unsigned not null;Query OK, 0 rows affected (0.13 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> desc test0004;+----------+----------------------+------+-----+---------+-------+| Field    | Type                 | Null | Key | Default | Extra |+----------+----------------------+------+-----+---------+-------+| username | varchar(10)          | NO   | UNI | NULL    |       || pid      | smallint(5) unsigned | YES  |     | NULL    |       || id       | smallint(5) unsigned | NO   | PRI | 0       |       || age      | tinyint(3) unsigned  | NO   |     | NULL    |       |+----------+----------------------+------+-----+---------+-------+4 rows in set (0.00 sec)mysql> alter table test0004 alter age set default 15;Query OK, 0 rows affected (0.02 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> desc test0004;+----------+----------------------+------+-----+---------+-------+| Field    | Type                 | Null | Key | Default | Extra |+----------+----------------------+------+-----+---------+-------+| username | varchar(10)          | NO   | UNI | NULL    |       || pid      | smallint(5) unsigned | YES  |     | NULL    |       || id       | smallint(5) unsigned | NO   | PRI | 0       |       || age      | tinyint(3) unsigned  | NO   |     | 15      |       |+----------+----------------------+------+-----+---------+-------+4 rows in set (0.00 sec)mysql> alter table test0004 alter age drop default;Query OK, 0 rows affected (0.02 sec)Records: 0  Duplicates: 0  Warnings: 0

（2）外键束缚
一个表可以有一个或多个外键；保证数据的一致性完整性；定义外键之后，不
允许删除另一个表中具有关联关系的记录。

主表：对于两个表具有关联关系的，具有主键的表；
从表：对于两个表具有关联关系的，具有外键的表；
constraint <外建名> foreign key<字段名> references <主表名> 主键列

mysql> create table test06    -> (id int(11) primary key,    -> name varchar(30) not null);Query OK, 0 rows affected (0.16 sec)

mysql> create table test07 (id int(11) primary key, name varchar(30), constraint test0607 foreign key(id) references test06(id) );Query OK, 0 rows affected (0.19 sec)

加深：
首先创一个provin1表

mysql> create table provin1( id smallint unsigned primary key auto_increment, pnaame varchar(20) not null );Query OK, 0 rows affected (0.09 sec)mysql> show create table provin1\G;*************************** 1. row ***************************       Table: provin1Create Table: CREATE TABLE `provin1` (  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,  `pname` varchar(20) NOT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec)

再创建一个test0003的表

mysql> create table test0003( id smallint unsigned primary key auto_increment, ussername varchar(10) not null, pid smallint unsigned, foreign key (pid) referencess provin1 (id) on delete cascade);Query OK, 0 rows affected (0.08 sec)mysql> show create table test0003\G;*************************** 1. row ***************************       Table: test0003Create Table: CREATE TABLE `test0003` (  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,  `username` varchar(10) NOT NULL,  `pid` smallint(5) unsigned DEFAULT NULL,  PRIMARY KEY (`id`),  KEY `pid` (`pid`),  CONSTRAINT `test0003_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `provin1` (`id`) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.01 sec)

向provin1中插入数据

mysql> insert provin1 (pname) values ('A');Query OK, 1 row affected (0.00 sec)mysql> insert provin1 (pname) values ('b');Query OK, 1 row affected (0.00 sec)mysql> insert provin1 (pname) values ('C');Query OK, 1 row affected (0.00 sec)mysql> select * from provin1;+----+-------+| id | pname |+----+-------+|  1 | A     ||  3 | b     ||  5 | C     |+----+-------+3 rows in set (0.00 sec)

向test0003中插入数据

mysql> insert test0003(username,pid) values ('tom',3);                          Query OK, 1 row affected (0.00 sec)mysql> insert test0003(username,pid) values ('lichao',5);Query OK, 1 row affected (0.00 sec)mysql> insert test0003(username,pid) values ('chenchen',1);Query OK, 1 row affected (0.00 sec)mysql> insert test0003(username,pid) values ('cat',3);Query OK, 1 row affected (0.00 sec)mysql> insert test0003(username,pid) values ('nihao',7);ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`chenchen`.`test0003`, CONSTRAINT`test0003_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `provin1` (`id`) ON DELETE CASCADE)

我们看到插入pid =7的时候出错了，错误原因是因为pid所对应provin1表中没有7这个id，所以他会报错，我们来看看test0003表中数据

mysql> select * from test0003;+----+----------+------+| id | username | pid  |+----+----------+------+|  1 | tom      |    3 ||  3 | lichao   |    5 ||  5 | chenchen |    1 ||  7 | cat      |    3 |+----+----------+------+4 rows in set (0.01 sec)

那么我们来去除一下provin1里边的id为3的字段来查看test0003表中的变化

mysql> delete from provin1 where id = 3;Query OK, 1 row affected (0.00 sec)mysql> select * from provin1;+----+-------+| id | pname |+----+-------+|  1 | A     ||  5 | C     |+----+-------+2 rows in set (0.00 sec)mysql> select * from test0003;+----+----------+------+| id | username | pid  |+----+----------+------+|  3 | lichao   |    5 ||  5 | chenchen |    1 |+----+----------+------+2 rows in set (0.00 sec)

我们看到去除provin1中的id为3的数据后test0003表中对应的pid为3的字段消失，此验证了外键束缚中的 cascade功能。
cascade：从父表删除或更新且自动删除或跟新子表中匹配的行.

下边我会整理其他约束和mysql表操作文档，陆续更新中，本文禁止转载，个人总结不易，请谅解