*** 约束保证数据的完整性和一致性
*** 约束分为表级约束和列级约束
*** 约束类型包括:
NOT NULL(非空约束)
PRIMARY KEY(主键约束)
UNIQUE KEY(唯一约束)
DEFAULT(默认约束)
FOREIGN KEY(外键约束)
1.外键约束的要求解析
1 [mysqld] 2 default-storage-engine=INNODB
mysql> USE test; mysql> CREATE TABLE provinces( -> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, -> pname VARCHAR(20) NOT NULL -> ); mysql> SHOW CREATE TABLE provinces; +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | tb0 | CREATE TABLE `provinces` ( `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT, `pname` varchar(20) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
mysql> CREATE TABLE users( -> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, -> username VARCHAR(10) NOT NULL, -> pid SMALLINT UNSIGNED, -> FOREIGN KEY (pid) REFERENCES provinces (id) -> );
mysql> SHOW INDEXES FROM provinces\G; *************************** 1. row *************************** Table: provinces Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1 Column_name: id Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: 1 row in set (0.00 sec)
mysql> SHOW INDEXES FROM users\G; *************************** 1. row *************************** Table: users Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1 Column_name: id Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: *************************** 2. row *************************** Table: users Non_unique: 1 Key_name: pid Seq_in_index: 1 Column_name: pid Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: 2 rows in set (0.00 sec)
mysql> CREATE TABLE provinces( -> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, -> pname VARCHAR(20) NOT NULL -> ); mysql> CREATE TABLE users1( -> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, -> username VARCHAR(10) NOT NULL, -> pid SMALLINT UNSIGNED, -> FOREIGN KEY (pid) REFERENCES provinces (id) ON DELETE CASCADE -> ); mysql> INSERT provinces(pname) VALUES('Tom'); mysql> INSERT provinces(pname) VALUES('John'); mysql> INSERT provinces(pname) VALUES('Driver'); mysql> INSERT users1(username,pid) VALUES('Huang',2); mysql> INSERT users1(username,pid) VALUES('Li',3); mysql> INSERT users1(username,pid) VALUES('Pan',3); mysql> INSERT users1(username,pid) VALUES('He',1); mysql> INSERT users1(username,pid) VALUES('Long',2); mysql> SELECT * FROM users1; +----+----------+------+ | id | username | pid | +----+----------+------+ | 1 | Huang | 2 | | 2 | Li | 3 | | 5 | Pan | 3 | | 6 | He | 1 | | 7 | Long | 2 | +----+----------+------+ mysql> SELECT * FROM provinces; +----+--------+ | id | pname | +----+--------+ | 1 | Tom | | 2 | John | | 3 | Driver | +----+--------+ mysql> DELETE FROM provinces WHERE id=3; mysql> SELECT * FROM provinces; +----+-------+ | id | pname | +----+-------+ | 1 | Tom | | 2 | John | +----+-------+ mysql> SELECT * FROM users1; +----+----------+------+ | id | username | pid | +----+----------+------+ | 1 | Huang | 2 | | 6 | He | 1 | | 7 | Long | 2 | +----+----------+------+
ALTER TABLE tbl_name(数据表名称) ADD [COLUMN] col_name(列名) column_definition(列定义) [FIRST(插入列到最前面)|AFTER(插入列到指定列的后方) col_name(列名)(如果不写FIRST和AFTER,则插入到最后一列)]
mysql> SHOW COLUMNS FROM users1; +----------+----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+----------------------+------+-----+---------+----------------+ | id | smallint(5) unsigned | NO | PRI | NULL | auto_increment | | username | varchar(10) | NO | | NULL | | | pid | smallint(5) unsigned | YES | MUL | NULL | | +----------+----------------------+------+-----+---------+----------------+
mysql> ALTER TABLE users1 ADD age TINYINT UNSIGNED NOT NULL DEFAULT 10; mysql> SHOW COLUMNS FROM users1; +----------+----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+----------------------+------+-----+---------+----------------+ | id | smallint(5) unsigned | NO | PRI | NULL | auto_increment | | username | varchar(10) | NO | | NULL | | | pid | smallint(5) unsigned | YES | MUL | NULL | | | age | tinyint(3) unsigned | NO | | 10 | | +----------+----------------------+------+-----+---------+----------------+
mysql> ALTER TABLE users1 ADD password VARCHAR(32) NOT NULL AFTER username; mysql> SHOW COLUMNS FROM users1; +----------+----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+----------------------+------+-----+---------+----------------+ | id | smallint(5) unsigned | NO | PRI | NULL | auto_increment | | username | varchar(10) | NO | | NULL | | | password | varchar(32) | NO | | NULL | | | pid | smallint(5) unsigned | YES | MUL | NULL | | | age | tinyint(3) unsigned | NO | | 10 | | +----------+----------------------+------+-----+---------+----------------+
mysql> ALTER TABLE users1 ADD truename VARCHAR(32) NOT NULL FIRST; mysql> SHOW COLUMNS FROM users1; +----------+----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+----------------------+------+-----+---------+----------------+ | truename | varchar(32) | NO | | NULL | | | id | smallint(5) unsigned | NO | PRI | NULL | auto_increment | | username | varchar(10) | NO | | NULL | | | password | varchar(32) | NO | | NULL | | | pid | smallint(5) unsigned | YES | MUL | NULL | | | age | tinyint(3) unsigned | NO | | 10 | | +----------+----------------------+------+-----+---------+----------------+
ALTER TABLE tbl_name(数据表名称) DROP [COLUMN] col_name(列名)
mysql> ALTER TABLE users1 DROP truename; mysql> SHOW COLUMNS FROM users1; +----------+----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+----------------------+------+-----+---------+----------------+ | id | smallint(5) unsigned | NO | PRI | NULL | auto_increment | | username | varchar(10) | NO | | NULL | | | password | varchar(32) | NO | | NULL | | | pid | smallint(5) unsigned | YES | MUL | NULL | | | age | tinyint(3) unsigned | NO | | 10 | | +----------+----------------------+------+-----+---------+----------------+
mysql> ALTER TABLE users1 DROP age,DROP password; mysql> SHOW COLUMNS FROM users1; +----------+----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+----------------------+------+-----+---------+----------------+ | id | smallint(5) unsigned | NO | PRI | NULL | auto_increment | | username | varchar(10) | NO | | NULL | | | pid | smallint(5) unsigned | YES | MUL | NULL | | +----------+----------------------+------+-----+---------+----------------+
ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...) //添加 ALTER TABLE tbl_name DROP PRIMARY KEY //删除
ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [index_name] [index_type] (index_col_name,...) //添加 ALTER TABLE tbl_name DROP {INDEX|KEY} index_name //删除
ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,...) reference_definition //添加 ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol //删除
ALTER TABLE tbl_name ALTER [COLUMN] col_name {SET DEFAULT literal|DROP DEFAULT} //添加 mysql> ALTER TABLE tbl_name ALTER age DROP DEFAULT; //删除
mysql> CREATE TABLE users2( -> username VARCHAR(20) NOT NULL, -> pid SMALLINT UNSIGNED -> ); mysql> ALTER TABLE users2 ADD id SMALLINT UNSIGNED; mysql> ALTER TABLE users2 ADD CONSTRAINT pk_users2_id PRIMARY KEY (id); mysql> SHOW COLUMNS FROM users2; +----------+----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+----------------------+------+-----+---------+-------+ | username | varchar(20) | NO | | NULL | | | pid | smallint(5) unsigned | YES | | NULL | | | id | smallint(5) unsigned | NO | PRI | 0 | | +----------+----------------------+------+-----+---------+-------+
mysql> ALTER TABLE users2 DROP PRIMARY KEY;
mysql> ALTER TABLE users2 ADD CONSTRAINT OK_users2_username UNIQUE KEY (username);
mysql> SHOW INDEXES FROM users2\G; *************************** 1. row *************************** Table: users2 Non_unique: 0 Key_name: OK_users2_username Seq_in_index: 1 Column_name: username Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: *************************** 2. row *************************** Table: users2 Non_unique: 1 Key_name: pid Seq_in_index: 1 Column_name: pid Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: 2 rows in set (0.00 sec) mysql> ALTER TABLE users2 DROP INDEX OK_users2_username; mysql> SHOW INDEXES FROM users2\G; *************************** 1. row *************************** Table: users2 Non_unique: 1 Key_name: pid Seq_in_index: 1 Column_name: pid Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: 1 row in set (0.00 sec)
mysql> ALTER TABLE users2 ADD FOREIGN KEY (pid) REFERENCES provinces (id);
mysql> SHOW CREATE TABLE users2; +--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | users2 | CREATE TABLE `users2` ( `username` varchar(20) NOT NULL, `pid` smallint(5) unsigned DEFAULT NULL, `id` smallint(5) unsigned NOT NULL DEFAULT '0', `age` tinyint(4) NOT NULL, KEY `pid` (`pid`), CONSTRAINT `users2_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `provinces` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ mysql> ALTER TABLE users2 DROP FOREIGN KEY users2_ibfk_1;
mysql> ALTER TABLE users2 ADD age TINYINT NOT NULL; mysql> SHOW COLUMNS FROM users2; +----------+----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+----------------------+------+-----+---------+-------+ | username | varchar(20) | NO | UNI | NULL | | | pid | smallint(5) unsigned | YES | MUL | NULL | | | id | smallint(5) unsigned | NO | PRI | 0 | | | age | tinyint(4) | NO | | NULL | | +----------+----------------------+------+-----+---------+-------+ mysql> ALTER TABLE users2 ALTER age SET DEFAULT 15; mysql> SHOW COLUMNS FROM users2; +----------+----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+----------------------+------+-----+---------+-------+ | username | varchar(20) | NO | UNI | NULL | | | pid | smallint(5) unsigned | YES | MUL | NULL | | | id | smallint(5) unsigned | NO | PRI | 0 | | | age | tinyint(4) | NO | | 15 | | +----------+----------------------+------+-----+---------+-------+
mysql> ALTER TABLE users2 ALTER age DROP DEFAULT; mysql> SHOW COLUMNS FROM users2; +----------+----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+----------------------+------+-----+---------+-------+ | username | varchar(20) | NO | UNI | NULL | | | pid | smallint(5) unsigned | YES | MUL | NULL | | | id | smallint(5) unsigned | NO | PRI | 0 | | | age | tinyint(4) | NO | | NULL | | +----------+----------------------+------+-----+---------+-------+
ALTER TABLE tbl_name MODIFY [COLUMN] col_name column_definition [FIRST|AFTER col_name]
ALTER TABLE tbl_name CHANGE [COLUMN] old_col_name new_col_name column_dufinition [FIRST|AFTER col_name]
ALTER TABLE old_tbl_name RENAME new_tbl_name
RENAME TABLE old_tbl_name TO new_tbl_name [,tbl_name2 TO new_tbl_name2]...