Results 1 to 9 of 9
  1. #1
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527

    Unanswered: MySQL foreign key info

    Does MySQL store FK info in the information schema? The schema seems to imply that it does but there's no useful data in there that I can find. I just want to see what the links are between tables automatically.

    Cheers

  2. #2
    Join Date
    Jul 2010
    Posts
    34
    mysql> select version();
    +--------------------+
    | version() |
    +--------------------+
    | 5.1.41-3ubuntu12.3 |
    +--------------------+
    1 row in set (0.00 sec)

    mysql> select CONSTRAINT_SCHEMA, TABLE_NAME, COLUMN_NAME, REFERENCED_TABLE_SCHEMA, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME from KEY_COLUMN_USAGE where REFERENCED_TABLE_SCHEMA is not null;

  3. #3
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Thank you - better late than never

  4. #4
    Join Date
    Aug 2010
    Posts
    1
    That's AWESOME! It not only took 1.5 years to get an answer, but the OP was actually still around to see the answer the same day. Classic. Just don't ask any questions about the PK...that might take even longer.
    Last edited by tsx1968; 08-06-10 at 23:36.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You gotta love it when the system werks!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by tsx1968 View Post
    That's AWESOME! It not only took 1.5 years to get an answer, but the OP was actually still around to see the answer the same day. Classic. Just don't ask any questions about the PK...that might take even longer.
    After the 1st year I was beginning to give up hope but I knew I just had to hang in there

  7. #7
    Join Date
    Jul 2010
    Posts
    34
    Hi, I guess it may seem a little weird to have replied to such an old thread. I came across it after reading Mikes thread on OTLT/EAV and then taking a look at his thread history for any other gems :-D

  8. #8
    Join Date
    Jul 2010
    Posts
    34
    Quote Originally Posted by tsx1968 View Post
    That's AWESOME! It not only took 1.5 years to get an answer, but the OP was actually still around to see the answer the same day. Classic. Just don't ask any questions about the PK...that might take even longer.

    mysql> select CONSTRAINT_SCHEMA, TABLE_SCHEMA, COLUMN_NAME from KEY_COLUMN_USAGE where CONSTRAINT_NAME = 'PRIMARY';


  9. #9
    Join Date
    Jul 2010
    Posts
    34
    Should be TABLE_NAME instead of TABLE_SCHEMA, sorry.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •