Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2004
    Location
    Bulgaria
    Posts
    22

    Question Unanswered: Which tables has reference to foreign key id?

    Hi, let's say I have table1(t1_a,t1_b and table2(t2_a,t2_b) table2 has columna t2_b that references t1_a with foreign key... both tables InnoDB of course.
    So I want to delete a record from table1 - but I would like to know if t1_a is somewhere referenced so I can give the user a warning..that this thingy is still in use......
    The approach of trying to delete...and parsing the error is not really an option I think. So besides, doing several selects(for each table that references t2_a) someone knows faster approach for this ?
    Looked also in information schema..but no such info provided there.
    Thanks

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by jmut
    Looked also in information schema..but no such info provided there.
    try the information schema KEY_COLUMN_USAGE table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2004
    Location
    Bulgaria
    Posts
    22
    Quote Originally Posted by r937
    try the information schema KEY_COLUMN_USAGE table
    Well, I did and only info I can get from there is which tables reference specific column I am interested in... but with this I am still left with N selects for each such table to determine if id is used.... This is my best shot so far... I was thinking to create view or something of that....but view is no performance booster so no clue.....thing is I'd like to be as quick as possible....

Posting Permissions

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