CODEWITHSUNDEEP
c#mysql
Stefan Edwards
Stefan Edwards

Reputation: 87

Get Foreign keys in the table

I created one script to display the foreign keys in the particular table.

SELECT  i.CONSTRAINT_NAME FROM information_schema.TABLE_CONSTRAINTS i
LEFT JOIN information_schema.KEY_COLUMN_USAGE k ON
i.CONSTRAINT_NAME = k.CONSTRAINT_NAME WHERE i.CONSTRAINT_TYPE = 'FOREIGN KEY'
AND i.TABLE_NAME = 'sample';

When I execute this command in my host, it displays the list of foreign key constraints found in all the databases with table name "sample".

But i need particular database, sample table foreign keys.

Upvotes: 0

Views: 2879

Answers (2)

Raphaël Althaus
Raphaël Althaus

Reputation: 60493

just add at the end

AND i.table_schema = '<the name of your database which is a schema>'

EDIT :

Change the left join to

LEFT JOIN information_schema.KEY_COLUMN_USAGE
   ON i.CONSTRAINT_NAME = k.CONSTRAINT_NAME AND 
      i.TABLE_SCHEMA = k.TABLE_SCHEMA

because you could find constraint with same name in different schemas.

what's a schema and a database in mysql

Upvotes: 1

Prasanna
Prasanna

Reputation: 4703

Need to select the database before run this query. Following may be helpful.

USE YourDatabaseName

SELECT  i.CONSTRAINT_NAME
FROM information_schema.TABLE_CONSTRAINTS i
LEFT JOIN information_schema.KEY_COLUMN_USAGE k
ON i.CONSTRAINT_NAME = k.CONSTRAINT_NAME
WHERE i.CONSTRAINT_TYPE = 'FOREIGN KEY'
AND i.TABLE_NAME = 'sample'

Upvotes: 0

Related Questions