Enforce a foreign-key constraint to columns of same table

Question

How to enforce a constraint of foreign key on columns of same table in SQL while entering values in the following table:

employee:

• empid number,
• manager number (must be an existing employee)

Solution 1

CREATE TABLE TABLE_NAME (
    `empid_number`    int     (  11) NOT NULL auto_increment,   
    `employee`        varchar ( 100) NOT NULL               ,
    `manager_number`  int     (  11) NOT NULL               ,
     PRIMARY KEY  (`empid_number`),
     CONSTRAINT `manager_references_employee`
     FOREIGN KEY (`manager_number`) REFERENCES (`empid_number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Hope it helps!

Solution 2

Oracle call this a self-referential integrity constraint. The documentation is here for a description,

You create a self-referential constraint in the same manner you would a normal one:

alter table employees
  add constraint employees_emp_man_fk
      foreign key ( manager_no )
      references employees ( emp_id )
   on delete set null
      ;

I'm assuming that your manager_no is nullable. I've added set null here as a delete cascade would probably wipe out a significant amount of your table.

I can't think of a better way of doing this. Deleting a manager should not result in the deletion of all their employees so you have to set null and have a trigger on the table to alert you to anyone with no manager.

I always like this site, which is good for simple references. and don't forget to have an index on the FK as well or Tom will yell at you :-).

One can also utilise standard Oracle syntax to create a self-referential FK in the create table statement, which would look like the following.

create table employees
 ( emp_id number
 , other_columns ...
 , manager_no number
 , constraint employees_pk 
    primary key (emp_id)
 , constraint employees_man_emp_fk
    foreign key ( manager_no )
    references employees ( emp_id )
    on delete set null
 );

EDIT:

In answer to @popstack's comment below:

Whilst you can do this in one statement not being able to alter a table is a fairly ridiculous state of affairs. You should definitely analyze a table that you're going to be selecting from and you will still want an index on the foreign key ( and possibly more columns and / or more indexes ) otherwise whenever you use the foreign key you're going to do a full table scan. See my link to asktom above.

If you're unable to alter a table then you should, in descending order of importance.

1. Find out how you can.
2. Change your DB design as a FK should have an index and if you can't have one then FKs are probably not the way to go. Maybe have a table of managers and a table of employees?

Solution 3

SELF REFERENCES QUERY...

Alter table table_name ADD constraints constraints_name foreign key(column_name1,column_name2..) references table_name(column_name1,column_name2...) ON DELETE CASCADE;

EX- ALTER TABLE Employee ADD CONSTRAINTS Fr_key( mgr_no) references employee(Emp_no) ON DELETE CASCADE;

Comments

[pop stack]

somewhat...! Any other methods/suggestions for oracle?

[instanceOfObject]

I never tried, but I think giving the TABLE_NAME instead of "parent_table_name" should work!!

[a_horse_with_no_name]

That's not a valid statement for Oracle

[instanceOfObject]

I suggested for MySql. As said, I don't have any idea about oracle.

[pop stack]

that may be a solution, BUT in my case, no 'triggers', no 'alters' allowed. simple 'create table' followed by 'inserts' are only needed. Now?

[Ben]

@popstack, I had too much to say so I added an edit to the answer.

[Adam Musch]

Pretty sure you're going to have to define a unique or foreign key constraint for the foreign key constraint to reference. And you could always omit the ON DELETE clause, which functions as would an ON DELETE RESTRICT clause in other RDBMS's, preventing deletion of a parent with child records.

[Ben]

@AdamMusch, you are of course correct about the PK. I've updated the answer. I don't like not doing anything after a delete though; if you don't it provides no benefits over set null and will leave you with non-obviously orphaned records in the table.

[Adam Musch]

@Ben: If you don't specify either ON DELETE CASCADE or ON DELETE SET NULL in Oracle, it will prevent you from deleting the parent, which prevents the non-obviously orphaned records in the first place by throwing ORA-02292.

[Sled]

@popstack why did you select a MySQL-only answer for an Oracle question? You should unselect this answer and select the other answer by Ben.