mutual declarations produce Error 1064

[Morten Gulbrandsen]

USE company;

# SHOW INNODB STATUS;

DROP TABLE IF EXISTS DEPARTMENT;

CREATE TABLE DEPARTMENT
(
DNAME VARCHAR(15) NOT NULL,
DNUMBER INT NOT NULL,
MGRSSN CHAR(9) NOT NULL,
MGRSTARTDATE DATE,

PRIMARY KEY (DNUMBER),
UNIQUE (DNAME),

INDEX (MGRSSN), # between employee and department
FOREIGN KEY (MGRSSN), # a sort of mutually declaration
REFERENCES EMPLOYEE(SSN) # this is declared in employee

)TYPE = INNODB;
DROP TABLE IF EXISTS EMPLOYEE;

CREATE TABLE EMPLOYEE
(
FNAME VARCHAR(15) NOT NULL,
MINIT CHAR,
LNAME VARCHAR(15) NOT NULL,
SSN CHAR(9) NOT NULL,
BDATE DATE,
ADDRESS VARCHAR(30),
SEX CHAR,
SALARY DECIMAL(10,2),
SUPERSSN CHAR(9),
DNO INT NOT NULL,

PRIMARY KEY (SSN),
INDEX (SUPERSSN, SSN),

FOREIGN KEY (SUPERSSN)
REFERENCES EMPLOYEE(SSN),

INDEX (DNO, DNUMBER),
FOREIGN KEY (DNO)
REFERENCES DEPARTMENT(DNUMBER) # this is declared in department
)TYPE = INNODB;

===
ERROR 1064 at line 9: You have an error in your SQL syntax. Check the
manual th
at corresponds to your MySQL server version for the right syntax to use
near '

REFERENCES EMPLOYEE(SSN)
)TYPE = INNODB' at line 10
Bye

In short

I have

Department
Dnumber
References employee (ssn)

Employee
Ssn
References department(dnumber)

What can be done , please ?

Yours sincerely

Morten Gulbrandsen

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/my***********...ie.nctu.edu.tw