mysql 給表添加唯一限制、聯合唯一限制，指定唯一限制的名字

琛ㄧ粨鏋?

[sql]聽 view plain 聽copy 聽

1. FIELD聽聽聽聽聽聽聽聽聽聽TYPE聽聽聽聽聽聽聽聽聽聽COLLATION聽聽聽聽聽聽聽NULL聽聽聽聽KEY聽聽聽聽聽DEFAULT聽聽Extra聽聽聽聽聽聽聽聽聽聽聽PRIVILEGES聽聽聽聽聽聽聽聽聽聽聽聽COMMENT聽聽
2. -------------聽聽------------聽聽--------------聽聽------聽聽------聽聽-------聽聽--------------聽聽--------------------聽聽-------聽聽
3. id聽聽聽聽聽聽聽聽聽聽聽聽聽BIGINT(20)聽聽聽聽(NULL)聽聽聽聽聽聽聽聽聽聽NO聽聽聽聽聽聽PRI聽聽聽聽聽(NULL)聽聽聽AUTO_INCREMENT聽聽SELECT,INSERT,UPDATE聽聽聽聽聽聽聽聽聽聽聽
4. resource_name聽聽VARCHAR(128)聽聽gbk_chinese_ci聽聽YES聽聽聽聽聽聽聽聽聽聽聽聽聽(NULL)聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽SELECT,INSERT,UPDATE聽聽聽聽聽聽聽聽聽聽聽
5. resource_type聽聽TINYINT(4)聽聽聽聽(NULL)聽聽聽聽聽聽聽聽聽聽YES聽聽聽聽聽聽聽聽聽聽聽聽聽(NULL)聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽SELECT,INSERT,UPDATE聽聽聽

缁檙esource_name鍜宺esource_type娣誨姞鑱斿悎鍞竴绾︽潫

[sql]聽 view plain 聽copy 聽

1. ALTER聽TABLE聽jw_resource聽聽
2. ADD聽UNIQUE聽KEY(resource_name,聽resource_type);聽聽

缁撴灉 show create table jw_resource;

[javascript]聽 view plain 聽copy 聽

1. CREATE聽TABLE聽`jw_resource`聽(聽聽
2. 聽聽`id`聽BIGINT(20)聽NOT聽NULL聽AUTO_INCREMENT,聽聽
3. 聽聽`resource_name`聽VARCHAR(128)聽DEFAULT聽NULL,聽聽
4. 聽聽`resource_type`聽TINYINT(4)聽DEFAULT聽NULL,聽聽
5. 聽聽PRIMARY聽KEY聽(`id`),聽聽
6. 聽聽UNIQUE聽KEY聽`resource_name`聽(`resource_name`,`resource_type`)聽聽
7. )聽ENGINE=INNODB聽AUTO_INCREMENT=2聽DEFAULT聽CHARSET=gbk聽聽

鍒犻櫎鍞竴绾︽潫

[sql]聽 view plain 聽copy 聽

1. ALTER聽TABLE聽jw_role聽DROP聽INDEX聽resource_name;聽聽

澧炲姞涓€涓敮涓€绾︽潫锛屽苟涓旂粰鍞竴绾︽潫璧峰悕瀛楋紵

鍘熻〃缁撴瀯锛?

[html]聽 view plain 聽copy 聽

1. mysql>聽show聽create聽table聽test_table;聽聽
2. +------------+--------------------------------------------------------------------------------------------------------------------------------------------聽聽
3. ---------------------------------+聽聽
4. |聽Table聽聽聽聽聽聽|聽Create聽Table聽聽
5. 聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽|聽聽
6. +------------+--------------------------------------------------------------------------------------------------------------------------------------------聽聽
7. ---------------------------------+聽聽
8. |聽test_table聽|聽CREATE聽TABLE聽`test_table`聽(聽聽
9. 聽聽`id`聽int(50)聽NOT聽NULL聽AUTO_INCREMENT,聽聽
10. 聽聽`kk`聽varchar(30)聽DEFAULT聽NULL,聽聽
11. 聽聽PRIMARY聽KEY聽(`id`)聽聽
12. )聽ENGINE=InnoDB聽AUTO_INCREMENT=2聽DEFAULT聽CHARSET=latin1聽|聽聽
13. +------------+--------------------------------------------------------------------------------------------------------------------------------------------聽聽
14. ---------------------------------+聽聽
15. 1聽row聽in聽set聽(0.00聽sec)聽聽

澧炲姞鍞竴绾︽潫鐨勮鍙ワ紝缁欏敮涓€绾︽潫璧峰悕鍙仛 kk_uq锛?

[html]聽 view plain 聽copy 聽

1. mysql>聽alter聽table聽test_table聽add聽unique聽key聽`kk_uq`聽(聽kk);聽聽

鏌ョ湅缁撴灉

[html]聽 view plain 聽copy 聽

1. mysql>聽show聽create聽table聽test_table;聽聽
2. +------------+--------------------------------------------------------------------------------------------------------------------------------------------聽聽
3. --------------------------------------------------------------+聽聽
4. |聽Table聽聽聽聽聽聽|聽Create聽Table聽聽
5. 聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽|聽聽
6. +------------+--------------------------------------------------------------------------------------------------------------------------------------------聽聽
7. --------------------------------------------------------------+聽聽
8. |聽test_table聽|聽CREATE聽TABLE聽`test_table`聽(聽聽
9. 聽聽`id`聽int(50)聽NOT聽NULL聽AUTO_INCREMENT,聽聽
10. 聽聽`kk`聽varchar(30)聽DEFAULT聽NULL,聽聽
11. 聽聽PRIMARY聽KEY聽(`id`),聽聽
12. 聽聽UNIQUE聽KEY聽`kk_uq`聽(`kk`)聽聽
13. )聽ENGINE=InnoDB聽AUTO_INCREMENT=2聽DEFAULT聽CHARSET=latin1聽|聽聽
14. +------------+--------------------------------------------------------------------------------------------------------------------------------------------聽聽
15. --------------------------------------------------------------+聽聽
16. 1聽row聽in聽set聽(0.00聽sec) 聽