3 messages in com.mysql.lists.mysqlRe: error 150 / FOREIGN KEY constraint
FromSent OnAttachments
Lieven De Keyzer28 May 2005 12:50 
Michael Stassen28 May 2005 14:33 
Lieven De Keyzer28 May 2005 14:35 
Subject:Re: error 150 / FOREIGN KEY constraint
From:Lieven De Keyzer (liev@hotmail.com)
Date:05/28/2005 02:35:52 PM
List:com.mysql.lists.mysql

Thanks for the reply, but I just found out about it :) This is my table now, and it is accepted without errors:

CREATE TABLE owner ( parent_id INTEGER NOT NULL, owner VARCHAR(25) NOT NULL, PRIMARY KEY (parent_id), INDEX (parent_id), FOREIGN KEY (parent_id) REFERENCES folder(folder_id) ON DELETE CASCADE, INDEX (owner), FOREIGN KEY (owner) REFERENCES account(username) ON DELETE CASCADE) TYPE = InnoDB;

From: Stijn Verholen <sver@skynet.be>

To: Lieven De Keyzer <liev@hotmail.com> Subject: Re: error 150 / FOREIGN KEY constraint Date: Sat, 28 May 2005 23:03:28 +0200

Lieven,

try: CREATE TABLE owner ( id INT NOT NULL AUTO_INCREMENT parent_id INTEGER, owner VARCHAR(25), PRIMARY KEY (id), INDEX parent_fk (parent_id), FOREIGN KEY (parent_id) REFERENCES folder(folder_id) ON UPDATE CASCADE ON DELETE CASCADE, INDEX owner_fk (owner), FOREIGN KEY (owner) REFERENCES account(username) ON UPDATE CASCADE ON DELETE CASCADE ) TYPE=INNODB;

I can't say if there are any other errors, but you have to create an index for each foreign key constraint you create (in the order they appear in your create table statement). The extra column (id) is there because i'm not sure if a primary key can be foreign at the same time in mysql.

greetz,

Stijn Verholen

Lieven De Keyzer wrote:

This is a script I'm trying to execute. My mysql version is 4.1.10.

SET FOREIGN_KEY_CHECKS = 0; DROP TABLE IF EXISTS owner; DROP TABLE IF EXISTS folder; DROP TABLE IF EXISTS account; DROP TABLE IF EXISTS role;

CREATE TABLE role ( role_id INTEGER NOT NULL, rolename VARCHAR(25) NOT NULL, PRIMARY KEY (role_id)) TYPE = InnoDB;

CREATE TABLE account ( username VARCHAR(25) NOT NULL, password VARCHAR(80) NOT NULL, email VARCHAR(80) NOT NULL, first_name VARCHAR(80) NOT NULL, last_name VARCHAR(80) NOT NULL, role_id INTEGER NOT NULL, PRIMARY KEY (username)) TYPE = InnoDB;

CREATE TABLE folder ( folder_id INTEGER NOT NULL AUTO_INCREMENT, parent_id INTEGER, foldername VARCHAR(80), PRIMARY KEY (folder_id)) TYPE = InnoDB;

CREATE TABLE owner ( parent_id INTEGER NOT NULL, owner VARCHAR(25) NOT NULL, PRIMARY KEY (parent_id), FOREIGN KEY (parent_id) REFERENCES folder(folder_id) ON DELETE CASCADE, FOREIGN KEY (owner) REFERENCES account(username) ON DELETE CASCADE) TYPE = InnoDB;

And this is the output:

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

ERROR 1005 (00000): Can't create table './lddekeyz/owner.frm' (errno: 150)

It seems like the last FOREIGN KEY of the 'owner' table is not correctly formed:

$perror 150 MySQL error: 150 = Foreign key constraint is incorrectly formed

But I can't see anything wrong with it.