Enforce a foreign-key constraint to columns of same table

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?

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;

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!