How can I define circular referential integrity constraints?

0 votes

3 answers

565 views

mariadb foreign-key referential-integrity

                          I have two tables mutually referencing each other, and I get this error when trying to insert into one of them: "Foreign key constraint is incorrectly formed".

I have been stuck for three weeks this assignment and can't seem to get anywhere or find someone with database experience to help.

I have attached all three queries but only receive an error on the last query where I create the table "DEPARTMENTS"

    CREATE DATABASE k0310855_AMAZON;

    USE k0310855_AMAZON;

my tables:

    CREATE TABLE EMPLOYEE (
    EMP_ID int PRIMARY KEY,
    EMP_SALARY int,
    EMP_PHONE varchar(255),
    EMP_DEPARTMENT varchar(255)
    );

and:

    CREATE TABLE CHILDREN (
        CHI_NAME int,
        CHI_AGE int,
        EMP_ID int,
        CONSTRAINT CHILDREN PRIMARY KEY (EMP_ID, CHI_NAME)
    );

and:

    CREATE TABLE DEPARTMENTS 
    (
      EMP_DEPARTMENT int PRIMARY KEY,
      DEP_NAME varchar(255),
      DEP_BUDGET int,
      DEP_MANAGER varchar(255),
      CONSTRAINT FOREIGN KEY (DEP_MANAGER) REFERENCES EMPLOYEE 
    (EMP_DEPARTMENT) 
    );**

I am receiving the following error:

    ERROR 1005 (HY000): Can't create table k0310855_AMAZON.DEPARTMENTS (errno: 150 "Foreign key constraint is incorrectly formed")

Asked by Jonathan Davis (1 rep)

Dec 4, 2021, 09:46 PM
Last activity: Dec 13, 2022, 09:03 AM

How can I define circular referential integrity constraints?

Related Questions