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.
Foregin keys have to be indexed in both tables, as explained in the
manual
<http://dev.mysql.com/doc/mysql/en/innodb-foreign-key-constraints.html>.
You need an index on owner.owner before you can create the foreign key
constraint.
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
