Michael Lugo Michael Lugo - 27 days ago 7
MySQL Question

What have I done wrong here

I am relatively new to the database world so bare with me. I'm just trying to add foreign key constraints and I keep getting error 1215 "cannot add foreign key constraint".

CREATE TABLE InProcessSamples
(
SampleNumber Int(6),
WorkOrder Int(8),
DueDate Date,
BeginsTesting Date,
FinishedTesting Date,
CONSTRAINT fk_sample_number FOREIGN KEY(SampleNumber) REFERENCES AllRecords(SampleNumber),
CONSTRAINT fk_work_order FOREIGN KEY(WorkOrder) REFERENCES SamplesReceived(WorkOrder)
);

CREATE TABLE SamplesReceived
(
WorkOrder Int(8) PRIMARY KEY,
SampleNumber Int(6),
RecTimeStamp DateTime,
PartNumber Int(10),
Description Char(36),
CONSTRAINT fk_sample_number FOREIGN KEY(SampleNumber) REFERENCES AllRecords(SampleNumber),
CONSTRAINT fk_part_number FOREIGN KEY(PartNumber) REFERENCES PartNumbers(PartNumber)
);

CREATE TABLE AllRecords
(
SampleNumber Int(6) PRIMARY KEY,
WorkOrder Int(8),
DueDate Date,
BeginsTesting Date,
FinishedTesting Date,
RecTimeStamp DateTime,
MeasurementOne Double,
MeasurementTwo Double,
PassDielectric Char(3),
PassedAllTest Char(3),
CONSTRAINT fk_work_order FOREIGN KEY(WorkOrder) REFERENCES SamplesReceived(WorkOrder),
CONSTRAINT fk_part_number FOREIGN KEY(PartNumber) REFERENCES PartNumbers(PartNumber)
);

CREATE TABLE PartNumbers
(
PartNumber Int(10) PRIMARY KEY,
Description Char(36)
);

Answer

Regardless of whether your design is off or not, you can't declare a foreign key reference to a table you haven't created yet.

CREATE TABLE statements are evaluated in order from top to bottom of your SQL script. As MySQL is trying to create the first table, the second and third tables don't exist yet. So there's nothing for the foreign keys to reference.

You should create tables in an order that allows the referenced table to exist before you create the table that has the foreign key to reference it. In this case, you have a circular dependency, so the only way to do it is to create either SamplesReceived or AllRecords without one of its foreign keys, and then go back afterward and add that foreign key.

  1. CREATE TABLE PartNumbers, because it's needed by SamplesReceived and AllRecords
  2. CREATE TABLE SamplesReceived, because it's needed by InProcessSamples and AllRecords
  3. CREATE TABLE AllRecords, because it's needed by InProcessSamples
  4. ALTER TABLE SamplesReceived ADD FOREIGN KEY(SampleNumber) REFERENCES AllRecords(SampleNumber);
  5. CREATE TABLE InProcessSamples

That's if the circular reference is really needed.

But as other folks have answered, perhaps your circular reference isn't really a good design.

The circular reference could be needed; what it does in your case is enforce that for every row in SamplesReceived, you must have a matching row in AllRecords, and vice-versa, every row in AllRecords must have a matching row in SamplesReceived.

I don't know why that's important in your application. It might be, but you haven't told us anything about the workflow you're trying to model so I can't tell.