Show constraints on tables command

mysql foreign-keys
Captain Claptrap picture Captain Claptrap · Oct 23, 2010 · Viewed 248.4k times · Source

I have tables that I've tried setting PK FK relationships on but I want to verify this. How can I show the PK/FK restraints? I saw this manual page, but it does not show examples and my google search was fruitless also. My database is credentialing1 and my constrained tables are practices and cred_insurances.

Answer

Adrian Smith picture Adrian Smith · Oct 23, 2010

I use

SHOW CREATE TABLE mytable;

This shows you the SQL statement necessary to receate mytable in its current form. You can see all the columns and their types (like DESC) but it also shows you constraint information (and table type, charset, etc.).

Related questions

Add Foreign Key to existing table

I want to add a Foreign Key to a table called "katalog". ALTER TABLE katalog ADD CONSTRAINT `fk_katalog_sprache` FOREIGN KEY (`Sprache`) REFERENCES `Sprache` (`ID`) ON DELETE SET NULL ON UPDATE SET NULL; When I try to do this, …

Read More
How to truncate a foreign key constrained table?

Why doesn't a TRUNCATE on mygroup work? Even though I have ON DELETE CASCADE SET I get: ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint (mytest.instance, CONSTRAINT instance_ibfk_1 FOREIGN KEY (GroupID) REFERENCES mytest.mygroup (ID)) …

Read More
MySQL Error 1215: Cannot add foreign key constraint

I am trying to forward engineer my new schema onto my db server, but I can't figure out why I am getting this error. I've tried to search for the answer here, but everything I've found has said to either …

Read More