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 <[EMAIL PROTECTED]>
To: Lieven De Keyzer <[EMAIL PROTECTED]>
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.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
