Azer Rtyu Azer Rtyu - 7 months ago 56
SQL Question

Error creating foreign key on <TableName> (check data types)

I want to create to tables in mysql, te first table is called Catégorie and the second is called Article as :

(Article) 0..* ------------- 1 (Catégorie)

but I get this error :


Error creating foreign key on idCat (check data types)


and this is the code I tried :

CREATE TABLE IF NOT EXISTS `Catégorie` (
`idCat` int(11) NOT NULL,
`libCat` varchar(50) DEFAULT NULL,
PRIMARY KEY (`idCat`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


CREATE TABLE IF NOT EXISTS `Article` (
`codeArt` int(11) NOT NULL,
`desgArt` int(11) DEFAULT NULL,
`etatArt` varchar(50) DEFAULT NULL,
`qteArt` int(11) DEFAULT '1',
`qteseulmin` varchar(50) DEFAULT NULL,
`idCat` int(11) NOT NULL,
PRIMARY KEY (`codeArt`),
KEY `idCat` (`idCat`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


ALTER TABLE `Article` ADD FOREIGN KEY ( `idCat` ) REFERENCES `GestionStock`.`Catégorie` (
`idCat`
) ON DELETE SET NULL ON UPDATE CASCADE ;

Answer

You declared column Article.idCat as NOT NULL, and then you are trying to create foreign key with ON DELETE SET NULL action.

Change column or foreign key definition.