1

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)
);
4
  • 1
    Please show your Guns table Commented Nov 21, 2013 at 7:13
  • One of the reason could be the series in which you are creating the tables (creating children before parents) set foreign_key_checks=0 at the starting of your DDL script.So the order by creation doesn't create problem. Commented Nov 21, 2013 at 7:21
  • I tried that already, it didn't work. I created purchases first, then guns, then tried sales and it didn't work. Commented Nov 21, 2013 at 7:24
  • 1
    SerialSold has to be unique, so you can never sell the same gun twice in the lifetime of your application? This might be desired functionality, but I can think of scenario's where you end up with unsellable items because your software doesn't allow selling it... If I buy a gun, sell it back to you, you can't sell it anymore. Commented Nov 21, 2013 at 8:19

1 Answer 1

9

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.

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.