重庆思庄Oracle、Redhat认证学习论坛

标题: mysql查找约束的sql语句 [打印本页]

作者: 郑全 时间: 2015-4-11 12:18
标题: mysql查找约束的sql语句

比如：sztech下面的一张表emp,查它有几个约束：

mysql> use sztech
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> show create table emp\G
*************************** 1. row ***************************
Table: emp
Create Table: CREATE TABLE `emp` (
`empid` int(11) NOT NULL DEFAULT '0',
`last_name` varchar(20) DEFAULT NULL,
`salary` double DEFAULT NULL,
`hire_date` date DEFAULT NULL,
PRIMARY KEY (`empid`),
KEY `idx_emp_empid` (`empid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> alter table emp add primary key (empid);

mysql> select CONSTRAINT_SCHEMA, CONSTRAINT_NAME,TABLE_NAME,CONSTRAINT_TYPE from information_schema.TABLE_CONSTRAINTS where TABLE_NAME='emp';
+-------------------+-----------------+------------+-----------------+
| CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_NAME | CONSTRAINT_TYPE |
+-------------------+-----------------+------------+-----------------+
| sztech | PRIMARY | emp | PRIMARY KEY |
+-------------------+-----------------+------------+-----------------+
1 row in set (0.00 sec)

欢迎光临重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/)