mysql simple problem with foreign keys

Question

I have two tables:

Table Author: ID (INTEGER), author_name (VARCHAR), first_name (VARCHAR), last_name (VARCHAR), preferred_name (VARCHAR).

Table CoAuthored: ID (INTEGER) author1ID (INTEGER), author2ID (INTEGER), paper_ID (INTEGER) (ID of the co-authored paper referenced by this link)

I want to add foreign key constraints such that author1ID and author2ID in CoAuthored refer to ID in Author. I defined the tables as such:

CREATE TABLE Author(ID INT, author_name VARCHAR(100), preferred_name VARCHAR(100), first_name VARCHAR(100), last_name VARCHAR(100), PRIMARY KEY(ID)) ENGINE=INNODB;

CREATE TABLE CoAuthored(ID INT, author1ID INT, author2ID INT, paper_ID INT);

This is my attempt at creating the foreign key:

ALTER TABLE CoAuthored ADD foreign key (author1ID) references Author(ID) on delete cascade on update cascade;

This executes just fine, but when I try to add a a tuple which contains an author1ID that doesn't exist in ID, it allows me to do it, meaning the foreign key constraint isn't working.

What do I need to do differently? I tried making both tables ENGINE=INNODB in the create statements, but then I'd get an ERROR 1005 can't create table.

Answer 1

from: http://articles.techrepublic.com.com/5100-10878_11-6035435.html

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

1. Both tables must be of the InnoDB table type.

2. The fields used in the foreign key relationship must be indexed.

3. The fields used in the foreign key relationship must be similar in data type.

Important: For non-InnoDB tables, the FOREIGN KEY clause is ignored.

Answer 2

Can you try 'ENGINE=InnoDB'

Foreign key constraints/Key Constraints work with InnoDB Tables only :)

--Cheers

Answer 3

First of all: You need to define all tables involved with foreign keys to be InnoDB. MyISAM simply doesn't support foreign keys.

Your problem here (Error 1005) is a bit confusing. If you do an ALTER TABLE, it means that there is a value in one of the foreign key fields that has no reference in another table.

When creating tables, make sure, that you are creating the "parent" table first, then the second and third table.