CODEWITHSUNDEEP
mysqlrecursion
ilos28
ilos28

Reputation: 103

Recursive table

I'm trying to create a table with a recursive column, but It doesn't work. Later I tried it and it did work, but the I dropped the table because of a problem and now I don't know what I did. The table's name is "security" and this is the script:

create table security (
id_emplo int(6) ZEROFILL NOT NULL,
id_boss varchar(10) DEFAULT NULL,
TIP int(5) NOT NULL,
security_division varchar(40),
PRIMARY KEY (id_emplo),
FOREIGN KEY (id_emplo) REFERENCES employees (id_emple) ON DELETE RESTRICT ON UPDATE CASCADE,
FOREIGN KEY (id_boss) REFERENCES security (id_emple) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=INNODB;

I have another table called "employees". If I try to create the table with only "id_emplo" REFERENCES employess... It doesn't have any problem.

Upvotes: 2

Views: 41

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You have to be very careful about types and declarations. The following should work:

create table security (
    id_emplo int(6) ZEROFILL NOT NULL,
    id_boss int(6) ZEROFILL DEFAULT NULL,
    TIP int(5) NOT NULL,
    security_division varchar(40),
    PRIMARY KEY (id_emplo),
    FOREIGN KEY (id_emplo) REFERENCES employees (id_emple) ON DELETE RESTRICT ON UPDATE CASCADE,
    FOREIGN KEY (id_boss) REFERENCES security (id_emplo) ON DELETE RESTRICT ON UPDATE CASCADE
);

Here is the SQL Fiddle.

You had two main problems:

  1. You had the name of the column incorrect. It should be id_emplo rather than id_emple (although I give foreign key columns the same name as the column they refer to when I can).
  2. The types were incompatible.

Upvotes: 1

Related Questions