Hello Programmers,
Thank you for the kind advice,
I have another small problem with MySQL I'd like to investigate,
All I get is this:
ERROR 1005 at line 47:
Can't create table '.\company\#sql-40c_4.frm' (errno: 150)
What I attempt to do is this:
C:\mysql\bin>mysql -u elmasri -pnavathe -vvv company < Company_08.sql >
out.txt
ERROR 1005 at line 47: Can't create table '.\company\#sql-40c_4.frm'
(errno: 150
)
input code is this as we all know:
USE company;
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 DEFAULT 1,
PRIMARY KEY (SSN),
INDEX (SUPERSSN),
INDEX (DNO)
)TYPE = INNODB;
DESCRIBE EMPLOYEE;
DROP TABLE IF EXISTS DEPARTMENT;
CREATE TABLE DEPARTMENT
(
DNAME VARCHAR(15) NOT NULL,
DNUMBER INT NOT NULL,
MGRSSN CHAR(9) NOT NULL DEFAULT '888665555',
MGRSTARTDATE DATE,
PRIMARY KEY (DNUMBER),
UNIQUE (DNAME),
INDEX (MGRSSN)
)TYPE = INNODB;
DESCRIBE DEPARTMENT;
ALTER TABLE EMPLOYEE
ADD FOREIGN KEY (SUPERSSN) REFERENCES EMPLOYEE(SSN)
ON DELETE SET NULL
ON UPDATE CASCADE;
ALTER TABLE EMPLOYEE # here is the buggy code
ADD FOREIGN KEY (DNO) REFERENCES DEPARTMENT(DNUMBER)
ON DELETE SET DEFAULT
ON UPDATE CASCADE;
ALTER TABLE DEPARTMENT
ADD FOREIGN KEY (MGRSSN) REFERENCES EMPLOYEE(SSN)
ON DELETE SET DEFAULT
ON UPDATE CASCADE ;
SHOW CREATE TABLE EMPLOYEE;
SHOW CREATE TABLE DEPARTMENT;
SHOW TABLE STATUS FROM company LIKE "EMPLOYEE";
SHOW TABLE STATUS FROM company LIKE "DEPARTMENT";
Output is this:
--------------
DROP TABLE IF EXISTS EMPLOYEE
--------------
Query OK, 0 rows affected (0.02 sec)
--------------
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 DEFAULT 1,
PRIMARY KEY (SSN),
INDEX (SUPERSSN),
INDEX (DNO)
)TYPE = INNODB
--------------
Query OK, 0 rows affected (0.00 sec)
--------------
DESCRIBE EMPLOYEE
--------------
+----------+---------------+-------------------+------+-----+---------+-
------+
| Field | Type | Collation | Null | Key | Default |
Extra |
+----------+---------------+-------------------+------+-----+---------+-
------+
| FNAME | varchar(15) | latin1_swedish_ci | | | |
|
| MINIT | char(1) | latin1_swedish_ci | YES | | NULL |
|
| LNAME | varchar(15) | latin1_swedish_ci | | | |
|
| SSN | varchar(9) | latin1_swedish_ci | | PRI | |
|
| BDATE | date | latin1_swedish_ci | YES | | NULL |
|
| ADDRESS | varchar(30) | latin1_swedish_ci | YES | | NULL |
|
| SEX | char(1) | latin1_swedish_ci | YES | | NULL |
|
| SALARY | decimal(10,2) | binary | YES | | NULL |
|
| SUPERSSN | varchar(9) | latin1_swedish_ci | YES | MUL | NULL |
|
| DNO | int(11) | binary | | MUL | 1 |
|
+----------+---------------+-------------------+------+-----+---------+-
------+
10 rows in set (0.00 sec)
--------------
DROP TABLE IF EXISTS DEPARTMENT
--------------
Query OK, 0 rows affected (0.00 sec)
--------------
CREATE TABLE DEPARTMENT
(
DNAME VARCHAR(15) NOT NULL,
DNUMBER INT NOT NULL,
MGRSSN CHAR(9) NOT NULL DEFAULT '888665555',
MGRSTARTDATE DATE,
PRIMARY KEY (DNUMBER),
UNIQUE (DNAME),
INDEX (MGRSSN)
)TYPE = INNODB
--------------
Query OK, 0 rows affected (0.02 sec)
--------------
DESCRIBE DEPARTMENT
--------------
+--------------+-------------+-------------------+------+-----+---------
--+-------+
| Field | Type | Collation | Null | Key | Default
| Extra |
+--------------+-------------+-------------------+------+-----+---------
--+-------+
| DNAME | varchar(15) | latin1_swedish_ci | | UNI |
| |
| DNUMBER | int(11) | binary | | PRI | 0
| |
| MGRSSN | varchar(9) | latin1_swedish_ci | | MUL |
888665555 | |
| MGRSTARTDATE | date | latin1_swedish_ci | YES | | NULL
| |
+--------------+-------------+-------------------+------+-----+---------
--+-------+
4 rows in set (0.00 sec)
--------------
ALTER TABLE EMPLOYEE
ADD FOREIGN KEY (SUPERSSN) REFERENCES EMPLOYEE(SSN)
ON DELETE SET NULL
ON UPDATE CASCADE
--------------
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
--------------
ALTER TABLE EMPLOYEE
ADD FOREIGN KEY (DNO) REFERENCES DEPARTMENT(DNUMBER)
ON DELETE SET DEFAULT
ON UPDATE CASCADE
--------------
Bye
Please ,
what else can I do ?
Alter Table add foreign key runs fine,
alter table X add foreign key referencec Y
with X!=Y simply woun't compile !
All I can get is
alter table X
add foreign key
referencec X
No mutual references between any two different tables,
Or does it exist another solution or workaround ?
Yours Sincerely
Morten Gulbrandsen
The one who loves MySQL ;-)
-----Ursprüngliche Nachricht-----
Von: Fred van Engen [mailto:[EMAIL PROTECTED]
Gesendet: Mittwoch, 13. August 2003 15:36
An: Morten Gulbrandsen
Cc: 'Victoria Reznichenko'; [EMAIL PROTECTED]
Betreff: Re: mutual declarations produce Error 1064
Morten,
In your extremely long mail, I think I managed to find your question and
removed all other stuff.
On Wed, Aug 13, 2003 at 03:18:26PM +0200, Morten Gulbrandsen wrote:
> mysql>
> mysql> # from now on the coding causes trouble
> mysql>
> mysql> ALTER TABLE EMPLOYEE
> -> FOREIGN KEY (SUPERSSN) REFERENCES EMPLOYEE(SSN)
> -> ON DELETE SET NULL
> -> ON UPDATE CASCADE;
> ERROR 1064: You have an error in your SQL syntax. Check the manual
that
> corresp
> onds to your MySQL server version for the right syntax to use near
> 'FOREIGN KEY
> (SUPERSSN) REFERENCES EMPLOYEE(SSN)
> ON DELETE SET
> mysql>
6.5.4 ALTER TABLE Syntax
ALTER [IGNORE] TABLE tbl_name alter_specification [, alter_specification
...]
alter_specification:
ADD [COLUMN] create_definition [FIRST | AFTER column_name ]
| ADD [COLUMN] (create_definition, create_definition,...)
| ADD INDEX [index_name] (index_col_name,...)
| ADD PRIMARY KEY (index_col_name,...)
| ADD UNIQUE [index_name] (index_col_name,...)
| ADD FULLTEXT [index_name] (index_col_name,...)
| ADD [CONSTRAINT symbol] FOREIGN KEY [index_name]
(index_col_name,...)
[reference_definition]
| ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
| CHANGE [COLUMN] old_col_name create_definition
[FIRST | AFTER column_name]
| MODIFY [COLUMN] create_definition [FIRST | AFTER column_name]
| DROP [COLUMN] col_name
| DROP PRIMARY KEY
| DROP INDEX index_name
| DISABLE KEYS
| ENABLE KEYS
| RENAME [TO] new_tbl_name
| ORDER BY col
| table_options
So what it clearly tells you, is to use this (note the 'ADD'):
mysql> ALTER TABLE EMPLOYEE
-> ADD FOREIGN KEY (SUPERSSN) REFERENCES EMPLOYEE(SSN)
-> ON DELETE SET NULL
-> ON UPDATE CASCADE;
Regards,
Fred.
--
Fred van Engen XB Networks B.V.
email: [EMAIL PROTECTED] Televisieweg 2
tel: +31 36 5462400 1322 AC Almere
fax: +31 36 5462424 The Netherlands
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
