表结构
FIELDTYPECOLLATION NULL KEYDEFAULT Extra PRIVILEGES COMMENT------------- ------------ -------------- ------ ------ ------- -------------- -------------------- -------id BIGINT(20) (NULL)NOPRI(NULL) AUTO_INCREMENT SELECT,INSERT,UPDATE resource_name VARCHAR(128) gbk_chinese_ci YES (NULL) SELECT,INSERT,UPDATE resource_type TINYINT(4) (NULL)YES (NULL) SELECT,INSERT,UPDATE
给resource_name和resource_type添加联合唯一约束
ALTER TABLE jw_resourceADD UNIQUE KEY(resource_name, resource_type);
结果 show create table jw_resource;
CREATE TABLE `jw_resource` (`id` BIGINT(20) NOT NULL AUTO_INCREMENT,`resource_name` VARCHAR(128) DEFAULT NULL,`resource_type` TINYINT(4) DEFAULT NULL,PRIMARY KEY (`id`),UNIQUE KEY `resource_name` (`resource_name`,`resource_type`)) ENGINE=INNODB AUTO_INCREMENT=2 DEFAULT CHARSET=gbk
删除唯一约束
ALTER TABLE jw_role DROP INDEX resource_name;
增加一个唯一约束,并且给唯一约束起名字?
原表结构:
mysql> show create table test_table;+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table| Create Table|+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| test_table | CREATE TABLE `test_table` (`id` int(50) NOT NULL AUTO_INCREMENT,`kk` varchar(30) DEFAULT NULL,PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 |+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)
增加唯一约束的语句,给唯一约束起名叫做 kk_uq:
mysql> alter table test_table add unique key `kk_uq` ( kk);
查看结果
mysql> show create table test_table;+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table| Create Table|+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| test_table | CREATE TABLE `test_table` (`id` int(50) NOT NULL AUTO_INCREMENT,`kk` varchar(30) DEFAULT NULL,PRIMARY KEY (`id`),UNIQUE KEY `kk_uq` (`kk`)) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 |+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)