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)