Foriegn Keys

[glenmac]

Hi I've heard you can now use foriegn keys with Mysql DBs as long as you use Innodb tables. I've tried to get them to work with no success so far. All help or links would be greatly appreciated.

 

[sipps]

What does your create table statement look like? You need to put Type = Innodb; at the end of teh create table statement and index the foreign keys.

Example:

CREATE TABLE customer
(
Customer_ID INT AUTO_INCREMENT NOT NULL primary key,
Customer_Category_ID INT NOT NULL,
Customer_Title VARCHAR(5),
Customer_Fname VARCHAR(10) NOT NULL,
Customer_Lname VARCHAR(12) NOT NULL,
Customer_Address VARCHAR(80),
Customer_Town VARCHAR(20),
Customer_County VARCHAR(20),
Customer_Postcode CHAR(10),
Customer_Telephone_Number VARCHAR(15),
Customer_Email VARCHAR(25),
Customer_DOB DATE NOT NULL,
Customer_Driving_License VARCHAR(15) NOT NULL,
INDEX (Customer_Category_ID),
CONSTRAINT customer_Customer_Category_ID_fk FOREIGN KEY(Customer_Category_ID) REFERENCES customer_category(Customer_Category_ID)
) TYPE = InnoDB;

You can leave out the TYPE = Innodb; if your have set your MySQL to mysqlmax, which automatically makes every table you create Innodb I believe, but I had problesm with this so I just put on the Type at the end for safteys sake.

Hope this helps a little bit!

 

[glenmac]

Thanks I'll try that.