0

I've got this code to create an SQL table, however I'm facing a #1215 error. 

CREATE TABLE ‘Categorie’ (
‘catID’ int(11) NOT NULL AUTO_INCREMENT,
‘naam’ varchar(20) NOT NULL,
‘prioriteit’ int(2) NOT NULL,
‘subCatVan’ int(11) DEFAULT NULL,
PRIMARY KEY (‘catID’),
CONSTRAINT ‘subCatVan’ FOREIGN KEY (‘subCatVan’) REFERENCES Categorie

(‘catID’) ON DELETE SET NULL ON UPDATE CASCADE
);

Help is appreciated!

2 Answers2

0

As Jens already pointed out, there is an issue with your ticks. First, the identifier quote character in MySQL is the backtick (`) by default, second, you are not using ticks for the table name in your foreign key constraint.

Replace all ticks with backticks and your statement will work (even without ticks around the table name in the foreign key constraint). Leave all ticks out and your statement will work. Change REFERENCES Categorie to REFERENCES ‘Categorie’ and your statement will work (although probably not as expected).

I would recommend using backticks in all places as a good practice:

CREATE TABLE `Categorie` (
`catID` int(11) NOT NULL AUTO_INCREMENT,
`naam` varchar(20) NOT NULL,
`prioriteit` int(2) NOT NULL,
`subCatVan` int(11) DEFAULT NULL,
PRIMARY KEY (`catID`),
CONSTRAINT `subCatVan` FOREIGN KEY (`subCatVan`) REFERENCES `Categorie`

(`catID`) ON DELETE SET NULL ON UPDATE CASCADE
);

What currently happens is that you are actually not creating a table named Categorie, but instead a table named ‘Categorie’ (including the ticks). Because you are not using the same ticks in your foreign key constraint, MySQL looks for a table named Categorie without the ticks and thus cannot find the target for your reference.

Marvin
  • 13,325
  • 3
  • 51
  • 57
-1

try this

CREATE TABLE `Categorie` (
`catID` INT(11) NOT NULL AUTO_INCREMENT,
`naam` VARCHAR(20) NOT NULL,
`prioriteit` INT(2) NOT NULL,
`subCatVan` INT(11) DEFAULT NULL,
PRIMARY KEY (`catID`),
CONSTRAINT `subCatVan` FOREIGN KEY (`subCatVan`) REFERENCES Categorie

(`catID`) ON DELETE SET NULL ON UPDATE CASCADE
);
Navjot Singh
  • 514
  • 4
  • 14
  • Please explain your answer. – Jens Mar 17 '15 at 13:19
  • Could you explain, please? –  Mar 17 '15 at 13:28
  • @jens: Now i know why you ask me to explain.You put comment on given post. Answer is same as you add comment in post. I replaced all ticks with backticks in given post. sorry i didn't see the comment. – Navjot Singh Mar 18 '15 at 04:03
  • @NavjotSingh You should write your explanation in the answer so it is helpfull for all readers in the future. – Jens Mar 18 '15 at 06:12