Getting table metadata in MySQL

views:

951

answers:

+1 Q:

Getting table metadata in MySQL

I'm trying to find out how to get the following constraint information from a table in MySQL 5.0:

primary key
foreign keys and table references
unique columns

What is the syntax of the query or queries to do so? I have a feeling I'm close with this, but there is no example.

+1 A:

You should try it and see. INFORMATION_SCHEMA is part of some standard and is supported in a (mostly) similar way in other databases; this standard should be documented - you can look for that doc.

But mainly the way would be to create a bunch of test tables, and then have a look at INFORMATION_SCHEMA to see what's there.

MarkR 2009-02-04 07:39:26

The problem is that there is no example query syntax, though. That's all I really need. Other sections in the INFORMATION_SCHEMA chapter have syntax examples, but this one doesn't.

VirtuosiMedia 2009-02-04 07:41:57

@MarkR: You might like to refer to this: http://www.xcdsql.org/MySQL/information_schema/5.1/MySQL_5_1_INFORMATION_SCHEMA.html

David Grant 2009-02-04 07:50:19

+2 A:

The SHOW COLUMNS command will show you the primary key and unique columns for a table.

As for foreign keys, you could use something like the SHOW CREATE TABLE command which will output the DDL statements needed to replicate the table.

fluffels 2009-02-04 07:46:17

SHOW COLUMNS doesn't show the UNIQUE restraint from what I can tell (just primary and foreign keys), but I can use SHOW CREATE TABLE if I have to. I was just looking for a way to specifically isolate the constraints without returning other information.

VirtuosiMedia 2009-02-04 07:54:14

you can get the unique info from SHOW INDEX FROM table_name

enobrev 2009-02-04 07:59:11

Check the link I posted. Under the subheading detailing the "key" column it says that it can be either "PRI", "UNI", "MUL" or empty. "UNI" indicates a unique field.

fluffels 2009-02-04 08:04:11

Thanks enobrev, that helps

VirtuosiMedia 2009-02-04 08:05:07

You could "SELECT CONSTRAINT_TYPE FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME = 'foo'" or something like that?

fluffels 2009-02-04 08:09:24

@fluffels - When I run SHOW COLUMNS FROM table_name, or SHOW FULL COLUMNS FROM table_name, it will show the PRI and MUL, but I can't seem to get it to display UNI. I had also been thinking that MUL meant the foreign key, but I was mistaken in that regard.

VirtuosiMedia 2009-02-04 08:10:28

That's odd. Does the unique show up when you query the DDL? Perhaps something changed that attribute?

fluffels 2009-02-04 08:12:52

It shows up when I use the SHOW CREATE TABLE command, but not SHOW COLUMNS. Your other query just showed a table with the values PRIMARY KEY and UNIQUE, but no column names.

VirtuosiMedia 2009-02-04 08:16:42

Thanks for the help. I think I'm going to use the SHOW CREATE TABLE command because it has everything I need, I'll just need to parse it a little.

VirtuosiMedia 2009-02-04 08:22:43

Actually, I was able to do it all with SHOW INDEX, but it makes an assumption that foreign keys will not also be marked with the unique constraint. Is that a safe assumption?

VirtuosiMedia 2009-02-04 10:01:07

@VirtuosiMedia: I think that's a good assumption, because I see no reason why a foreign key would be marked unique. In that case, two related entities would be same, no?

fluffels 2009-02-04 10:18:11

To clarify, unique foreign keys would indicate a 1:1 mapping, in which I don't see much sense.

fluffels 2009-02-04 10:18:43

Yeah, that's what I figured as well. Thanks.

VirtuosiMedia 2009-02-04 10:23:33

ansaurus

tags:

views:

answers:

Getting table metadata in MySQL

related questions