Home > Software engineering >  SQL Query not working Foreign key constraint is incorrectly formed
SQL Query not working Foreign key constraint is incorrectly formed

Time:10-26

Can someone please explain why this SQL Query isn't working for me? I get this error: Foreign key constraint is incorrectly formed

DROP DATABASE IF EXISTS foodblog;

CREATE DATABASE foodblog;

USE foodblog;

CREATE TABLE posts (
    id int(11) AUTO_INCREMENT,
    titel varchar(255),
    datum DATETIME DEFAULT current_timestamp(),
    img_url varchar(255),
    inhoud text,
    auteur_id int(11),
    PRIMARY KEY (id),
    FOREIGN KEY (auteur_id) REFERENCES auteurs(id)
);

CREATE TABLE auteurs (
    id int(11) AUTO_INCREMENT,
    auteur varchar(255),
    PRIMARY KEY (id)
);

CodePudding user response:

Try re-ordering the queries. Because, when first query executes, the table auteurs would not be available. Here is the corrected code:

DROP DATABASE IF EXISTS foodblog;

CREATE DATABASE foodblog;

USE foodblog;

CREATE TABLE auteurs (
    id int(11) AUTO_INCREMENT,
    auteur varchar(255),
    PRIMARY KEY (id)
);

CREATE TABLE posts (
    id int(11) AUTO_INCREMENT,
    titel varchar(255),
    datum DATETIME DEFAULT current_timestamp(),
    img_url varchar(255),
    inhoud text,
    auteur_id int(11),
    PRIMARY KEY (id),
    FOREIGN KEY (auteur_id) REFERENCES auteurs(id)
);
  •  Tags:  
  • sql
  • Related