|
When I first started my database interfacing I discovered that MySQL supports foreign key contraints using the InnoDB storage engine.
One major problem is that none of the GUI tools allow for the addition or removing of foreign key contraints. You have to do this using SQL.
Now this can be quiet a daunting task if you have never done it before, but a bit of research using Google and the MySQL site provided to be more than enough to solve this problem.
When you try and alter a MySQL table using the GUI tools, it attempts to drop the table and recreate it. BIG mistake as the InnoDB engine will turn around and say "NO WAYS! You got a reference pointing here" and at that point you will get the infamous "ERROR 1217: Cannot delete or update a parent row: a foreign key constraint fails" or my personal favourite "ERROR 1091: Can't DROP 'FK'; check that column/key exists".
Right, now lets get down to business. Given the following table structures:
CREATE TABLE Company
(
Id BIGINT NOT NULL AUTO_INCREMENT,
CompanyName VARCHAR(50),
PRIMARY KEY (Id)
)ENGINE=InnoDB;
CREATE TABLE Customer
(
CompanyId BIGINT,
Email VARCHAR(50),
FirstName VARCHAR(50),
Id BIGINT NOT NULL AUTO_INCREMENT,
LastName VARCHAR(50),
PRIMARY KEY (Id),
UNIQUE (Email),
KEY (CompanyId)
)ENGINE=InnoDB;
As you can see in the Company table we have a column for the company the user is associated to. Now to add the foreign key we use the ALTER command. Something like:
ALTER TABLE Customer ADD CONSTRAINT FK_Customer_Company
FOREIGN KEY (CompanyId) REFERENCES Company (Id) ;
To break this down it is something like:
ALTER TABLE [table] ADD CONSTRAINT [UniqueName]
FOREIGN KEY ([fkColumn]) REFERENCES [ref table]([ref col]) ;
Okay the next step. Say you want to add a group table to the database and you want each customer to belong to a group. We add the table CustomerGroup which looks like:
CREATE TABLE CustomerGroup
(
Id BIGINT NOT NULL AUTO_INCREMENT,
GroupName VARCHAR(50),
PRIMARY KEY (Id),
UNIQUE (GroupName),
)ENGINE=InnoDB;
Now if you try and add anything to the customer table MySQL will laugh at you and send you packing. So here comes the fun part of giving MySQL the finger. First we need to add our column (which we will call CustomerGroupId) to the Customer table. We do that like so:
ALTER TABLE Customer ADD COLUMN CustomerGroupId BIGINT;
Now execute that command and viola! you have added the column, now for the next bit. Adding the constraint.
ALTER TABLE Customer ADD CONSTRAINT FK_Customer_CustomerGroup
FOREIGN KEY (CustomerGroupId)
REFERENCES CustomerGroup (Id) ;
Execute the command and away you go! Nice and simple really. MySQL Query browser allows you to execute SQL commands against tables so you don't have to use the command line. Most other front ends allow for this as well. Happy SQLing.
|
