CODEWITHSUNDEEP
mysqlsqlmysql-workbench
Rajesh Hatwar
Rajesh Hatwar

Reputation: 1933

MySQL : on creating foreign key geting error no : 150

the table ENUMSTATE refrying ENUMCOUNTRY but

This code results in this error: Error Code: 1005. Can't create table 'easylibdb1.enumstate' (errno: 150)

=> Enum Country

CREATE TABLE IF NOT EXISTS ENUMCOUNTRY(

    ID INT UNSIGNED NOT NULL AUTO_INCREMENT,
    NAME VARCHAR(50) DEFAULT '',
    SHORTNAME VARCHAR(50) DEFAULT '',
    STATUS BIT DEFAULT FALSE,
    PRIMARY KEY (ID)
)

=> Enum STATE

CREATE TABLE IF NOT EXISTS ENUMSTATE(

    ID INT UNSIGNED NOT NULL AUTO_INCREMENT,
    NAME VARCHAR(50) DEFAULT '',
    SHORTNAME VARCHAR(50) DEFAULT '',
    STATUS BIT DEFAULT FALSE,
    COUNTRYID INT,      
    PRIMARY KEY (ID)
    FOREIGN KEY (COUNTRYID) REFERENCES ENUMCOUNTRY(ID)  
)

Upvotes: 0

Views: 65

Answers (4)

Sathish D
Sathish D

Reputation: 5034

There is no comma after PRIMARY KEY (ID) in ENUMSTATE table. That is the reason you are getting syntax error.

Upvotes: 0

peterm
peterm

Reputation: 92785

Change table definition for ENUMSTATE with

CREATE TABLE IF NOT EXISTS ENUMSTATE(

    ID INT UNSIGNED NOT NULL AUTO_INCREMENT,
    NAME VARCHAR(50) DEFAULT '',
    SHORTNAME VARCHAR(50) DEFAULT '',
    STATUS BIT DEFAULT FALSE,
    COUNTRYID INT UNSIGNED,   -- < type should be UNSIGNED as PK in ENUMCOUNTRY  
    PRIMARY KEY (ID),  -- < you're missing comma here
    FOREIGN KEY (COUNTRYID) REFERENCES ENUMCOUNTRY(ID)  
);

Here is SQLFiddle demo

Using FOREIGN KEY Constraints
Corresponding columns in the foreign key and the referenced key must have similar data types. The size and sign of integer types must be the same. The length of string types need not be the same. For nonbinary (character) string columns, the character set and collation must be the same.

Upvotes: 2

Vishal Suthar
Vishal Suthar

Reputation: 17194

There're 2 mistakes in the ENUMSTATE

  1. You are missing comma after PRIMARY KEY (ID)

    PRIMARY KEY (ID),

  2. The Foreign key must have the same definition defined in a reference table (Primary key in reference table) with the same data type and the size.

    COUNTRYID INT UNSIGNED

Upvotes: 0

Andy Jones
Andy Jones

Reputation: 6275

You're missing a comma after PRIMARY KEY (ID) in your ENUMSTATE table

Upvotes: 0

Related Questions