修改约束
测试表
mysql> create table customer2(
-> customer_id char(8) not null default '',
-> name varchar(30),
-> address varchar(80) not null default '',
-> email varchar(50),
-> sex enum('男','女') not null,
-> )charset=utf8;
Query OK, 0 rows affected (0.07 sec)
mysql> desc customer2;
+-------------+-----------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-----------------+------+-----+---------+-------+
| customer_id | char(8) | NO | | | |
| name | varchar(30) | YES | | NULL | |
| address | varchar(80) | NO | | | |
| email | varchar(50) | YES | | NULL | |
| sex | enum('男','女') | NO | | NULL | |
+-------------+-----------------+------+-----+---------+-------+
5 rows in set (0.02 sec)
添加约束 primary key
// 客户号设置为主键
mysql> alter table customer2 add primary key(customer_id);
Query OK, 0 rows affected (0.63 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc customer2;
+-------------+-----------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-----------------+------+-----+---------+-------+
| customer_id | char(8) | NO | PRI | | |
| name | varchar(30) | YES | | NULL | |
| address | varchar(80) | NO | | | |
| email | varchar(50) | YES | | NULL | |
| sex | enum('男','女') | NO | | NULL | |
+-------------+-----------------+------+-----+---------+-------+
5 rows in set (0.02 sec)
添加约束 unique
//邮箱不能重复
//如果 添加unique约束时 emile 列中已经有重复 则会添加失败
mysql> alter table customer2 add unique(email);
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc customer2;
+-------------+-----------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-----------------+------+-----+---------+-------+
| customer_id | char(8) | NO | PRI | | |
| name | varchar(30) | YES | | NULL | |
| address | varchar(80) | NO | | | |
| email | varchar(50) | YES | UNI | NULL | |
| sex | enum('男','女') | NO | | NULL | |
+-------------+-----------------+------+-----+---------+-------+
5 rows in set (0.02 sec)
修改约束 / 字段
//客户姓名不能为空
mysql> alter table customer2 modify name varchar(30) not null default '';
Query OK, 0 rows affected (0.15 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc customer2;
+-------------+-----------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-----------------+------+-----+---------+-------+
| customer_id | char(8) | NO | PRI | | |
| name | varchar(30) | NO | | | |
| address | varchar(80) | NO | | | |
| email | varchar(50) | YES | | NULL | |
| sex | enum('男','女') | NO | | NULL | |
+-------------+-----------------+------+-----+---------+-------+
5 rows in set (0.02 sec)
修改约束 / 字段
//客户地址默认为 'china'
mysql> alter table customer2 modify address varchar(80) not null default 'china';
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc customer2;
+-------------+-----------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-----------------+------+-----+---------+-------+
| customer_id | char(8) | NO | PRI | | |
| name | varchar(30) | NO | | | |
| address | varchar(80) | NO | | china | |
| email | varchar(50) | YES | UNI | NULL | |
| sex | enum('男','女') | NO | | NULL | |
+-------------+-----------------+------+-----+---------+-------+
6 rows in set (0.02 sec)
删除约束
创建测试表
//主表 my_class
mysql> create table my_class (id int primary key,name varchar(20) not null default '');
Query OK, 0 rows affected (0.08 sec)
//从表 students
mysql> create table students (id int primary key,name varchar(20) not null default '',email varchar(20) not null unique,class_id int,foreign key(class_id) references my_class(id));
Query OK, 0 rows affected (0.07 sec)
//主表信息
mysql> show create table my_class;
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| my_class | CREATE TABLE `my_class` (
`id` int(11) NOT NULL,
`name` varchar(20) NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
//从表信息
mysql> show create table students;
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| students | CREATE TABLE `students` (
`id` int(11) NOT NULL,
`name` varchar(20) NOT NULL DEFAULT '',
`email` varchar(20) NOT NULL,
`class_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `email` (`email`),
KEY `class_id` (`class_id`),
CONSTRAINT `students_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `my_class` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
删除主键和外键
如果主键有一个外键指向它,会删除失败,除非先删除对应的外键
//删除my_class主键 失败 因为此时有一个外键指向它
mysql> alter table my_class drop primary key;
ERROR 1025 (HY000): Error on rename of '.\test\#sql-1158_14' to '.\test\my_class' (errno: 150)
//查看students的外键名称 该外键名称是 students_ibfk_1
//从表信息
mysql> show create table students;
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| students | CREATE TABLE `students` (
`id` int(11) NOT NULL,
`name` varchar(20) NOT NULL DEFAULT '',
`email` varchar(20) NOT NULL,
`class_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `email` (`email`),
KEY `class_id` (`class_id`),
CONSTRAINT `students_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `my_class` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
//删除students外键
mysql> alter table students drop foreign key students_ibfk_1;
Query OK, 0 rows affected (0.13 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table students;
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| students | CREATE TABLE `students` (
`id` int(11) NOT NULL,
`name` varchar(20) NOT NULL DEFAULT '',
`email` varchar(20) NOT NULL,
`class_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `email` (`email`),
KEY `class_id` (`class_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
//删除my_class主键 成功
mysql> alter table my_class drop primary key;
Query OK, 1 row affected (0.18 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> show create table my_class;
+----------+----------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------+----------------------------------------------------------------------------------------------------------------------------------+
| my_class | CREATE TABLE `my_class` (
`id` int(11) NOT NULL,
`name` varchar(20) NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+----------+----------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
删除 unique
//删除 students 表中的 email 字段的 unique 约束
mysql> alter table students drop unique(email);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'unique(email)' at line 1
//正确写法 用 index
mysql> alter table students drop index email;
Query OK, 0 rows affected (0.59 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table students;
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| students | CREATE TABLE `students` (
`id` int(11) NOT NULL,
`name` varchar(20) NOT NULL DEFAULT '',
`email` varchar(20) NOT NULL,
`class_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `class_id` (`class_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
删除其他约束可以使用修改约束完成
总结
以上所有操作都可使用 修改约束 / 字段 来代替,只是有的操作有专门方法,消耗资源更少