sql foreign key constraint fails

kpachopoulos · 02-16-2009, 03:02 PM

Hi,
i am trying to refresh my sql knowledge a little by using mysql. I have the following code:

Code:

DROP DATABASE IF EXISTS Store;
CREATE DATABASE Store;
USE Store;

CREATE TABLE Orders
(
  oid int NOT NULL,
  cid int NOT NULL,
  PRIMARY KEY (oid),
  FOREIGN KEY (cid) REFERENCES Customer (cid)
);


CREATE TABLE Customer
(
  cid int NOT NULL,
  PRIMARY KEY (cid)
);

I am able to insert a row in Order without the corresponding key in Customer. I do not get a complain. Why is that?

raconteur · 02-16-2009, 04:24 PM

In order to set up a foreign key relationship between two MySQL tables, three conditions must be met:

Both tables must be of the InnoDB table type.
The fields used in the foreign key relationship must be indexed.
The fields used in the foreign key relationship must be similar in data type.

I'm assuming you already use the InnoDB engine, and we can obviously see the data types are similar.

Try indexing the foreign key in the Orders table.

An afterthought occurs to me but I'd have to do more research to prove it... the table creation order may be significant.
All examples I've come across for creating tables with foreign keys create the referenced table first.

There is a brief but succinct tutorial on MySQL referential integrity here.

jlinkels · 02-17-2009, 06:49 PM

Raconteur, you have more knowledge about this subject than I do, but as a matter of fact tables are created using the myISAM engines by default.

jlinkels