Как применить ограничение внешнего ключа к столбцам одной и той же таблицы в SQL при вводе значений в следующую таблицу:
сотрудник:
- эмпидное число,
- номер менеджера (должен быть существующий сотрудник)
Как применить ограничение внешнего ключа к столбцам одной и той же таблицы в SQL при вводе значений в следующую таблицу:
сотрудник:
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
Надеюсь, поможет!
Oracle называет это ограничением самореферентной целостности. Документация находится здесь для описания,
Вы создаете самореферентное ограничение так же, как и обычное:
alter table employees
add constraint employees_emp_man_fk
foreign key ( manager_no )
references employees ( emp_id )
on delete set null
;
Я предполагаю, что ваш manager_no
имеет значение NULL. Я добавил сюда set null, так как delete cascade
, вероятно, уничтожит значительную часть вашей таблицы.
Я не могу придумать лучшего способа сделать это. Удаление менеджера не должно приводить к удалению всех его сотрудников, поэтому вам нужно set null
и иметь триггер на столе, чтобы предупредить вас о любом, у кого нет менеджера.
Мне всегда нравится этот сайт, который хорош для простых ссылок. и не забудьте также иметь указатель на FK или Том будет кричать на вас :-).
Можно также использовать стандартный синтаксис Oracle для создания самореферентного FK в операторе создания таблицы, который будет выглядеть следующим образом.
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
);
ИЗМЕНИТЬ:
В ответ на комментарий @popstack ниже:
Хотя вы можете сделать это в одном выражении, невозможность изменить таблицу является довольно нелепым положением дел. Вам обязательно следует проанализировать таблицу, из которой вы собираетесь выбирать, и вам все равно понадобится индекс по внешнему ключу (и, возможно, больше столбцов и/или больше индексов), иначе всякий раз, когда вы используете внешний ключ, который вы собираетесь делать полное сканирование таблицы. См. мою ссылку на asktom выше.
Если вы не можете изменить таблицу, сделайте это в порядке убывания важности.
ON DELETE
, которое функционирует так же, как и предложение ON DELETE RESTRICT
в других СУБД, предотвращая удаление родителя с дочерними записями.
- person Adam Musch; 09.01.2012
set null
и оставит вас с неочевидно потерянными записями в таблице.
- person Ben; 09.01.2012
ON DELETE CASCADE
, ни ON DELETE SET NULL
в Oracle, это не позволит вам удалить родителя, что в первую очередь предотвращает неочевидно потерянные записи, выбрасывая ORA-02292
.
- person Adam Musch; 09.01.2012
ЗАПРОС САМОРЕФЕРЕНЦИЙ...
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;