CODEWITHSUNDEEP
mysqlsqldatabaseforeign-keys
Optiq
Optiq

Reputation: 3182

Creating a table with foreign keys shows an error

I'm trying to create a table and am getting an error telling me there's something wrong around line 9. This is the code.

CREATE TABLE shirts_link (
    adult VARCHAR(1) NOT NULL,
    kids VARCHAR(1) NOT NULL,
    babies VARCHAR(1) NOT NULL,
    shirt_id INT(4) NOT NULL,
    size_id INT(4) NOT NULL,
    price_id INT(4) NOT NULL,
    PRIMARY KEY (shirt_id,size_id,price_id),
    FOREIGN KEY (shirt_id) REFERENCES shirts(id),
    FOREIGN KEY (size_id) REFERENCES shirt_sizes(id),
    FOREIGN KEY (price_id) REFERENCES shirt_prices(id)
    )ENGINE=INNODB;

here's the other tables I'm trying to link to..

CREATE TABLE shirts (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL,
    shirt_name VARCHAR(20) NOT NULL,
    men VARCHAR(10) NULL,
    women VARCHAR(10) NULL,
    boys VARCHAR(10) NULL,
    girls VARCHAR(10) NULL,
    babies VARCHAR(10) NULL,
)ENGINE=INNODB;

CREATE TABLE shirt_sizes (
    id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    size_name VARCHAR(10) NOT NULL
)ENGINE=INNODB;

CREATE TABLE shirt_prices (
        id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
        price_cat VARCHAR(10) NOT NULL,
        price NUMERIC(6,2) NOT NULL
    )ENGINE=INNODB;

Upvotes: 1

Views: 139

Answers (2)

John Woo
John Woo

Reputation: 263693

the problem is that you are wrapping table names and column names with single quotes. it shouldn't be or use backtick instead

FOREIGN KEY (shirt_id) REFERENCES shirts(id),
FOREIGN KEY (size_id) REFERENCES shirt_sizes(id),
FOREIGN KEY (price_id) REFERENCES shirt_prices(id)

or

FOREIGN KEY (`shirt_id`) REFERENCES `shirts`(`id`),
FOREIGN KEY (`size_id`) REFERENCES `shirt_sizes`(`id`),
FOREIGN KEY (`price_id`) REFERENCES `shirt_prices`(`id`)

but in this case, they are optional since non of they are MySQL reserved Keywords.

UPDATE 1

The data type of the keys must be the same with each other, declare these columns as UNSIGNED

shirt_id INT(4) UNSIGNED NOT NULL,
size_id INT(4) UNSIGNED NOT NULL,
price_id INT(4) UNSIGNED NOT NULL,

Upvotes: 5

Ahmed Kato
Ahmed Kato

Reputation: 1707

CREATE TABLE shirts_link (
    adult VARCHAR(1) NOT NULL,
    kids VARCHAR(1) NOT NULL,
    babies VARCHAR(1) NOT NULL,
    shirt_id INT(4) NOT NULL,
    size_id INT(4) NOT NULL,
    price_id INT(4) NOT NULL,
    PRIMARY KEY (shirt_id,size_id,price_id),
    FOREIGN KEY (shirt_id) REFERENCES shirts(id),
    FOREIGN KEY (size_id) REFERENCES shirt_sizes(id),
    FOREIGN KEY (price_id) REFERENCES shirt_prices(id)
    )ENGINE=INNODB;

it worked for me on sqlfiddle Here is my code:

CREATE TABLE shirts(
  id INT NOT NULL,
  PRIMARY KEY (id)
  );
CREATE TABLE shirts_link (
    adult VARCHAR(1) NOT NULL,
    kids VARCHAR(1) NOT NULL,
    babies VARCHAR(1) NOT NULL,
    shirt_id INT(4) NOT NULL,
    size_id INT(4) NOT NULL,
    price_id INT(4) NOT NULL,
    PRIMARY KEY (shirt_id,size_id,price_id),
    FOREIGN KEY (shirt_id) REFERENCES shirts(id)
    );

Upvotes: 3

Related Questions