CODEWITHSUNDEEP
mysqlsql
Laurent-P
Laurent-P

Reputation: 145

Can't create table because of foreign key mistake

I'm trying to create a database with products in it, and i've created a table that join products and stores, but my "CREATE TABLE" sentence don't work, I get a 1005 error with 150 errno, I've searched on different website, checked the case, the type, and nothing works.

Here's the code 

CREATE TABLE IF NOT EXISTS `products` (
  `barcode` bigint(20) NOT NULL,
  `name` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT 'Unnamed product',
  `allerg` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT 'z',
  `description` text CHARACTER SET utf8 COLLATE utf8_bin,
  CONSTRAINT pk_products PRIMARY KEY (`barcode`)
)

CREATE TABLE IF NOT EXISTS `stores` (
  `name` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT 'Unnamed store',
  `city` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT 'Not specified',
    `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    CONSTRAINT pk_stores PRIMARY KEY (`id`)
)

CREATE TABLE IF NOT EXISTS `contain` (
    `barcode` bigint(20) NOT NULL,
    `id` bigint(20) unsigned NOT NULL,  
    CONSTRAINT pk_barcode PRIMARY KEY (`barcode`,`id`),
    CONSTRAINT fk_bc FOREIGN KEY (`barcode`) 
    REFERENCES `products`(`barcode`) ON DELETE CASCADE ON UPDATE CASCADE,   
    CONSTRAINT fk_id FOREIGN KEY (`id`) 
    REFERENCES `stores`(`id`) ON DELETE CASCADE ON UPDATE CASCADE
)

Does some one see anything?

Thank you.

Upvotes: 0

Views: 50

Answers (1)

Bastian
Bastian

Reputation: 261

CREATE TABLE IF NOT EXISTS `products` (
  `barcode` bigint(20) NOT NULL,
  `name` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT 'Unnamed product',
  `allerg` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT 'z',
  `description` text CHARACTER SET utf8 COLLATE utf8_bin,
  CONSTRAINT pk_products PRIMARY KEY (`barcode`)
);

CREATE TABLE IF NOT EXISTS `stores` (
  `name` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT 'Unnamed store',
  `city` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT 'Not specified',
    `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    CONSTRAINT pk_stores PRIMARY KEY (`id`)
);

CREATE TABLE IF NOT EXISTS `contain` (
    `barcode` bigint(20) NOT NULL,
    `id` bigint(20) unsigned NOT NULL,  
    CONSTRAINT pk_barcode PRIMARY KEY (`barcode`,`id`),
    CONSTRAINT fk_bc FOREIGN KEY (`barcode`) 
    REFERENCES `products`(`barcode`) ON DELETE CASCADE ON UPDATE CASCADE,   
    CONSTRAINT fk_id FOREIGN KEY (`id`) 
    REFERENCES `stores`(`id`) ON DELETE CASCADE ON UPDATE CASCADE
);

Upvotes: 1

Related Questions