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.
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 = '[COLOR="Red"]$databasename[/COLOR]'
AND tc.table_name = '[COLOR="red"]$tablename[/COLOR]'
AND tc.constraint_name = 'primary'
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).
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.
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
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.
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 = '[COLOR="Red"]emily[/COLOR]'
AND tc.table_name = '[COLOR="red"]catkey[/COLOR]'
AND tc.constraint_name = 'primary'
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’
?
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
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?
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.)
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
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.
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)