CODEWITHSUNDEEP
mysqlforeign-keys
pvgoddijn
pvgoddijn

Reputation: 12898

Mysql show create constraint?

Is there a easy way to query a table for its constraints(foreignkeys specificaly) like show create table, but for the constraints only?

thanks,

pvgoddijn

Upvotes: 7

Views: 5719

Answers (4)

Alex Jasmin
Alex Jasmin

Reputation: 39496

To show only the foreign key constraints you can check the constraint_type in information_schema.table_constraints and get the affected columns in information_schema.key_column_usage via a join

SELECT b.table_name, b.column_name, b.constraint_name,
       b.referenced_table_name, b.referenced_column_name
FROM information_schema.table_constraints a
JOIN information_schema.key_column_usage b
ON a.table_schema = b.table_schema AND a.constraint_name = b.constraint_name
WHERE a.table_schema=database() AND a.constraint_type='FOREIGN KEY'
ORDER BY b.table_name, b.constraint_name;

Upvotes: 12

Ike Walker
Ike Walker

Reputation: 65547

select * from 
information_schema.KEY_COLUMN_USAGE 
where table_schema = <db_name> 
and table_name = <table_name>;

Upvotes: 1

Jonathan Czitkovics
Jonathan Czitkovics

Reputation: 1614

MySQL 5.1 Manual

SHOW TABLE STATUS FROM db_name LIKE 'tbl_name';

Upvotes: -2

northpole
northpole

Reputation: 10346

SHOW TABLE STATUS FROM db_name LIKE 'tbl_name';

The foreign key constraints are listed in the Comment column of the output.

Upvotes: -2

Related Questions