When i try to create a table called Sales, MySQL gives the following error. I can't tell what the problem is, and I glanced at http://dev.mysql.com/doc/refman/4.1/en/innodb-foreign-key-constraints.html to see what mistake i made, but I can't tell. I get this error in MySQL 5.6.11, but not in 5.1.67
Error
SQL query:
CREATE TABLE IF NOT EXISTS Sales(
name VARCHAR( 30 ) NOT NULL ,
address VARCHAR( 70 ) NOT NULL ,
serialsold VARCHAR( 20 ) UNIQUE NOT NULL ,
background_check VARCHAR( 9 ) NOT NULL ,
soldfor FLOAT NOT NULL ,
datesold TIMESTAMP DEFAULTNOW( ) ,
FOREIGN KEY ( serialsold ) REFERENCES Guns( serialnumber ) ON DELETE SET NULL ON UPDATE CASCADE ,
PRIMARY KEY ( background_check )
);
MySQL said: Documentation
#1215 - Cannot add foreign key constraint
my Guns and Purchases Tables:
CREATE TABLE IF NOT EXISTS Purchases
(
name VARCHAR(30) NOT NULL,
address VARCHAR(70) NOT NULL,
serialbought VARCHAR(20) UNIQUE NOT NULL,
boughtfor FLOAT,
datebought TIMESTAMP DEFAULT NOW(),
PRIMARY KEY (serialbought)
);
CREATE TABLE IF NOT EXISTS Guns
(
sequence INTEGER(4) NOT NULL AUTO_INCREMENT,
manufacturer VARCHAR(30) NOT NULL,
model VARCHAR(20) NOT NULL,
caliber VARCHAR(10) NOT NULL,
serialnumber VARCHAR(20) UNIQUE NOT NULL,
type VARCHAR(10) NOT NULl,
sellsfor FLOAT DEFAULT NULL,
FOREIGN KEY (serialnumber) REFERENCES Purchases(serialbought)
ON DELETE RESTRICT ON UPDATE RESTRICT,
PRIMARY KEY (sequence)
);
Gunstableforeign_key_checks=0at the starting of your DDL script.So the order by creation doesn't create problem.