The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Discovering Database structure question (MySQL)

Hello, is it possible to find out the relations of tables by directly querying the mysql database?

I've managed to get a list of Databases and list of Tables. I will also need a List of relations between the tables (for example, T1.X is FK to T2.Y).

Is it possible? Thanks
The Dude (Not Lebowski)
Monday, October 29, 2007
 
 
There are a couple of options.
If you are using mysql 5 you can use the information_schema to get that information. (check the manual for details)

Alternatively you can use "show create table TABLENAME" to see the create table command which should include the foreign key constraints.

This all assumes that your database uses INNODB tables because MYISAM tables don't enforce constraints so most people don't define them.

Monday, October 29, 2007
 
 
Use DESC <tablename> or DESCRIBE <tablename>
Ezani Send private email
Monday, October 29, 2007
 
 
Download the Free Community edition of the ModelRight tool here:

http://www.modelright.com/downloads.aspx

(Last download on the page).

It will give you a graphical view of your database structure with all the FK constraints between tables, etc.
Dan P
Tuesday, October 30, 2007
 
 
==>There are a couple of options. If you are using mysql 5 you can use the information_schema to get that information. (check the manual for details)

This all, of course, assumes that there are actually foreign key constraints in existence.

My experience, especially when it's a database that comes from unknown and undocumented systems, is that these really don't exist. There's a lot of related data that ain't enforced or even defined at the database level, but that exist, nontheless, and enforced via application code.

Not a good way to go, but databases 'round the world are full of this crap and you've gotta really dig in to find this stuff.
Sgt.Sausage
Tuesday, October 30, 2007
 
 
SELECT T.CONSTRAINT_SCHEMA, T.CONSTRAINT_NAME, K.TABLE_NAME, K.COLUMN_NAME, K.REFERENCED_TABLE_SCHEMA, K.REFERENCED_TABLE_NAME, K.REFERENCED_COLUMN_NAME
FROM information_schema.TABLE_CONSTRAINTS T
JOIN information_schema.KEY_COLUMN_USAGE K
ON T.CONSTRAINT_NAME = K.CONSTRAINT_NAME
WHERE T.CONSTRAINT_TYPE = 'FOREIGN KEY'
-- AND T.CONSTRAINT_SCHEMA = 'MYSCEMA'
-- AND K.TABLE_NAME = 'MYTABLE'
ORDER BY T.CONSTRAINT_SCHEMA, K.TABLE_NAME, K.COLUMN_NAME, T.CONSTRAINT_NAME;
Jason T
Tuesday, October 30, 2007
 
 
That query was in MySQL 5
Jason T
Tuesday, October 30, 2007
 
 
+1 to Sgt... I love one the apps I am babysitting. There are quite a few gems that I had to put my detective hat on to discover. Of course there are no documents on this!

When I first got the database in my hands, I fired up the old ER Studio to pull out a diagram. No lines. Hmph. I checked my settings and ran it again. Apparently, using SQL Server, these guys created a 150+ table database without using foreign keys... yeah. This was gonna be fun. Not.

Items.Order_PO -> PO_Table.PONumber
Reps.Branch -> Stores.Location
Ordermst.Rep_Location ->Stores.Location

One of my favorites:

Items.Item_Num is the line number, Items.Skey is the identity, but...
Item_Log.Item_Num -> Items.Skey

*yak*

Don't get me started on the code.
Jason T
Wednesday, October 31, 2007
 
 
"Not a good way to go, but databases 'round the world are full of this crap and you've gotta really dig in to find this stuff. "

Sometimes that type of thing is done on purpose. I know that to some of the "database purists" it seems completely illogical. But when your system isn't the one that is completely in control of creating the data then it may make sense to make relastionships be "implied" instead of "explicit".

For example, we have batch maintenance facilities at night where our customers will send down files exported from a corporate system. One file for items, one for prices, one for promotions, and on and on. But if we had an explicit foreign key constraint then a price might fail to import if the item doesn't already exist. And the item may be sitting in another file that is just waiting to be imported. So we don't want to throw away a perfectly good price and cause even more headache simply so that we can claim to have an explicit foreign key relationship that really provides very little additional benefit to us anyway.

So as always, you have to look at the situation before deciding which way to go. If the data is completely within your control then explicit foreign key constraints may be doable. But if you don't control the data then you may want to make those constraints implicit.
anon
Friday, November 02, 2007
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz