SitePoint Sponsor

User Tag List

Results 1 to 15 of 15
  1. #1
    SitePoint Evangelist
    Join Date
    Jun 2010
    Posts
    446
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    dynamically determining mysql key

    Can you dynamically determine whether a primary key has been set in a mysql table using php? If so how? I haven't been able to find a function or an approach.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Code:
    SELECT cu.ordinal_position
         , cu.column_name 
      FROM information_schema.table_constraints AS tc 
    INNER
      JOIN information_schema.key_column_usage AS cu 
        ON cu.constraint_name = tc.constraint_name 
       AND cu.table_schema = tc.table_schema
       AND cu.table_name = tc.table_name
     WHERE tc.table_schema = '$databasename'
       AND tc.table_name = '$tablename'
       AND tc.constraint_name = 'primary'
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict svcghost's Avatar
    Join Date
    Oct 2010
    Posts
    288
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The function that r937 supplied above works in achieving this. If you cannot tell, the query pulls data from the metadata tables (tables in the MySQL database that hold data about the user made tables).

  4. #4
    SitePoint Evangelist
    Join Date
    Jun 2010
    Posts
    446
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    What does cu.ordinal_position, cu.column_name refer to? It looks like you're using an object reference. I've not used objects in a query before.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    cu is a table alias, and is used to qualify which table those columns are in

    both tables in the query are assigned aliases using the AS keyword
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Evangelist
    Join Date
    Jun 2010
    Posts
    446
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Thanks, that helped a lot. I'm always learning something new, but I don't recognize how I'm to use the two AS statements. I now know that's where cu and tc are defined, but don't see how to complete the definition where my database = emily, table = catkey and the column = category_subcategory.

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Code:
    SELECT cu.ordinal_position
         , cu.column_name 
      FROM information_schema.table_constraints AS tc 
    INNER
      JOIN information_schema.key_column_usage AS cu 
        ON cu.constraint_name = tc.constraint_name 
       AND cu.table_schema = tc.table_schema
       AND cu.table_name = tc.table_name
     WHERE tc.table_schema = 'emily'
       AND tc.table_name = 'catkey'
       AND tc.constraint_name = 'primary'
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Evangelist
    Join Date
    Jun 2010
    Posts
    446
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Please confirm that the info from the result comes from info that would result from:

    SELECT * FROM INFORMATION_SCHEMA.table_constraints WHERE TABLE_SCHEMA='emily' AND TABLE_NAME='catkey'

    ?

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by nichemtktg View Post
    Please confirm ...
    your query will not actually show the column(s) that constitute the primary key

    that's why INFORMATION_SCHEMA.KEY_COLUMN_USAGE is also required
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Evangelist
    Join Date
    Jun 2010
    Posts
    446
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Please excuse me. That's actually what I thought I sent you in my last reply:
    SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA='emily' AND TABLE_NAME='catkey'

    You see, once I discovered (with your help) that I could use code such as:
    NFORMATION_SCHEMA.table_constraints
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE
    INFORMATION_SCHEMA.statistics
    etc

    My inclination would have been to do a query then a mysql_fetch_array.

    However, your code reminds me that a lot more can be done in the query so I'm working to completely understand it.

    Consequently, I need to ask about your code incrementally.

    SELECT cu.ordinal_position, cu.column_name

    tells me that somewhere in the available info is a key called ordinal_position and one called column_name and they will be SELECTed from an alias called cu. Right?

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by nichemtktg View Post
    ... tells me that somewhere in the available info is a key called ordinal_position and one called column_name and they will be SELECTed from an alias called cu. Right?
    exactly

    well, except that ordinal_position and column_name are columns, not keys (keys are something else)

    the cu qualifier tells you which table they're in

    i like "cu" because it stands for "column usage" (instead of aliases like x or t1, etc.)
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Evangelist
    Join Date
    Jun 2010
    Posts
    446
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    It might be a minor point, but how does the the column selection work? I called them keys because if I produced the info with mysql_fetch_array I would've had a resource with a key with which to work. What then, is the resource that your query is tapping? I thought it was a resource like an array.

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by nichemtktg View Post
    It might be a minor point, but how does the the column selection work? I called them keys because if I produced the info with mysql_fetch_array I would've had a resource with a key with which to work. What then, is the resource that your query is tapping? I thought it was a resource like an array.
    i'm pretty sure i did not understand your question at all

    sorry
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    SitePoint Evangelist
    Join Date
    Jun 2010
    Posts
    446
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Once I learned about the schemas I would've done two queries, two mysql_fetch_arrays and zeroed-in on the keys I wanted.

    You said you were selecting columns not keys which tells me that I probably don't understand what the schemas really are. I was inclined to think about them as an array-like resource like the resource that's produced by a query before mysql_fetch_array turns it into a genuine array.

    You're talking about selecting actual columns and I thought I need clarification about the schemas, but maybe not. Like I said, it may be nothing. If I didn't trigger anything, I'll ask for confirmation about the rest of your code.

    Thanks for your patience.

  15. #15
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    perhaps it makes more sense if i told you that the query is designed to retrieve data from some mysql tables

    these aren't your user-defined tables, like emily.catkey, they are mysql's tables in the INFORMATION_SCHEMA database, actual tables of columns of data that just happen to contain the metadata information about your user-defined tables

    so you run a query against the INFORMATION_SCHEMA tables, and the results are returned like any other query

    what you do with it after that, i dunno... ;o)
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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