CODEWITHSUNDEEP
mysqlsqlforeign-keyscreate-table
user3015939
user3015939

Reputation: 11

Cannot add foreign key constraint

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)
);

Upvotes: 1

Views: 3007

Answers (1)

Devart
Devart

Reputation: 121902

You defined a field serialsold as not-nullable -

serialsold VARCHAR( 20 ) UNIQUE NOT NULL

but then you are trying to add action with SET NULL clause -

ON DELETE SET NULL

Change one of these options and try to create table.

Upvotes: 9

Related Questions