In PhPMyAdmin - when you set up a referential constraint with a foreign key?

Question

which i've done, succesfully - how can you see the actual SQL query code itself ?

it shows you query code when you add new records to a table and allows you to select a foreign key for the new value youre adding (in my case, table 'drinks' has a foreign key which references the primary key of the table 'drink_types' )

so basicaly ive got 129 different drinks in a table called ' Drinks '.

i.e

Drink_ID (PK)--------Name--------Drink_Type (FK)

1 Stella Artois 3
2 Jack Daniels 5

(where '3' is the PK for drink type 'Beer' in the table 'drink types' , and '5' is the PK for drink type 'Whiskey' in the same table 'drink types').


table drink types looks like this


Type_ID--------Type
1 Wine
2 Ale
3 Beer
4 Rum
5 Whiskey
6 Champagne
7 Vodka
8 Bitter
9 Scotch
etc etc


PhPMyAdmin allows you to use 'relation view' or something, where you can create an index for a column value that behaves as a Foreign Key to a Primary Key in another table.

much the way ive done here in the example i just showed you.

As much as you'd like to have a glass of JD on the rocks, or a cold frosty beer - Please try not to day dream about beer and whiskey now - and focus on my question ^_^

lol

i want to see the SQL query CODE that shows the RELATIONSHIP that i set up between the two tables 'Drinks' and 'Drink Types'.

whats the best way to do that ?

anyone know ??

Also the constraints are that - in the table 'Drinks' a drink itself may not even be assigned to a type, so its value under the foreign key column can be nothing (Null is allowed).

oh and if i DELETE a drink_type from the 'drink type' table, i.e if i deleted the type 'whiskey' - then the record 'Jack Daniels' in the 'drinks' table REMAINS - but its type id gets set to null - so dont worry ! my whole thing is fine ! ^_^

just help ? where can i find the SQL query code for the relation i set up ?

while you're all answering my question - i'll just pop downstairs and pour myself a glass of Jack Daniels :-D

lol

Answer 1

Querying the system catalog tables would be the answer, I suppose. Haven't tried it with MySQL (I used to know the SYSIBM DB2 catalog inside and out, but I'm relatively new to MySQL). Perhaps something along the lines of:



select *

from information_schema.table_constraints

where table_schema = schema()

and table_name = 'drinks'

and constraint_type = 'FOREIGN KEY';



You can check the doc at:



http://dev.mysql.com/doc/refman/5.0/en/table-constraints-table.html

Answer 2

2 reasons, i think of of. a million) there is an assumption that application sturdy judgment could make up for what's lost by way of now no longer having ref integrity on the db ingredient. 2) i'm much less constructive approximately this, yet i think of of distant places places keys must be seen as one among those decadence--a function of "intense priced" products like sq. Server or Oracle. Decadence unbecoming to the open source crowd.

Answer 3

To get PHPmyadmin for free you can click here on the link http://bitly.com/1no5RNx

It's surely the leader program of its type

Bye Bye