MySQL > Linking tables?

I'm going crazy here... reasonably simple concept... however i have three books open, the MySQL manual and numerous webpages... can't seem to find any reference on how to add foreign keys...

have yet to see anything that says you can't (quite frankly, what's a database without foreign keys??)...

help me out before i pop a vein... =)

thanks

Make sure you are set up to use InnoDB table type and have specified in your SQL statement that you want to make the table an InnoDB table. Additionally the columns that are foreign keys need to be indexes as well.

Like this:

CREATE TABLE `some_table` (
`Some_Code1` bigint(20) NOT NULL default '0',
`Some_Code2` bigint(20) NOT NULL auto_increment,
`Field_Name` varchar(100) NOT NULL default '',
PRIMARY KEY (`Some_Code1`,`Some_Code2`),
KEY `some_table_IX1` (`Some_Code1`),
KEY `sometable_IX2` (`Some_Code2`),
FOREIGN KEY (`Some_Code1`) REFERENCES `some_parent_table` (`Some_Code1`)
) TYPE=InnoDB;


Sorry if this isn't too great of an example but it should be enough to get you started.

also you need MySQL Server 3.23.44 or better..

ctownsen357:

thanks... i read an example like that on mysql.net, but it didn't make sense to me...

someone actually explained to me that you do'nt have to necessarily set up constraints in MySQL. I've had a lot of practice with Oracle, and thus I was used to the way things operate there...

I'll read up on the other table-type (InnuDB or whatever?) and perhaps it's more along the lines of what I need.

For now i've set up my tables like this:

CREATE TABLE one
(one_id INT NOT NULL PRIMARY KEY,
desc VARCHAR);

CREATE TABLE two
(two_id INT NOT NULL PRIMARY KEY,
desc VARCHAR,
one_id INT);


then when i use it i would just join them in the SELECT statement... i was expecting a way to restrict constraints with normal tables... guess i'll read about that new kind...

thanks again!