12

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)
Sled
  • 18,541
  • 27
  • 119
  • 168
Pop Stack
  • 926
  • 4
  • 19
  • 27

3 Answers3

23

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?
Ben
  • 51,770
  • 36
  • 127
  • 149
  • that may be a solution, BUT in my case, no 'triggers', no 'alters' allowed. simple 'create table' followed by 'inserts' are only needed. Now? – Pop Stack Jan 07 '12 at 14:36
  • @popstack, I had too much to say so I added an edit to the answer. – Ben Jan 07 '12 at 15:14
  • 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. – Adam Musch Jan 09 '12 at 00:14
  • @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. – Ben Jan 09 '12 at 08:43
  • 2
    @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`. – Adam Musch Jan 09 '12 at 15:32
1

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;

legrandviking
  • 2,348
  • 1
  • 22
  • 29
pawan kumar
  • 11
  • 2
-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!

Sled
  • 18,541
  • 27
  • 119
  • 168
instanceOfObject
  • 2,936
  • 5
  • 49
  • 85