ON DELETE SET DEFAULT-未实现的功能 [英] ON DELETE SET DEFAULT - unimplemented feature

问题描述

创建表时.

错误消息显示SQL Error: ORA-03001: unimplemented feature

CREATE TABLE OrgChart
  (
    job_title VARCHAR(30) NOT NULL PRIMARY KEY,
    emp_nbr   INTEGER DEFAULT 0 NOT NULL REFERENCES Personnel(emp_nbr) ON
  DELETE SET DEFAULT ON
  UPDATE CASCADE,
    boss_emp_nbr INTEGER REFERENCES Personnel(emp_nbr),
    UNIQUE (emp_nbr, boss_emp_nbr),
    salary DECIMAL (12,4) NOT NULL CHECK (salary >= 0.00)
  );

我如何声明一个外键，该键允许更新从父表进行级联，并在删除父行时将该值设置为默认值(在这种情况下为0)?

How do I declare a foreign key that allows updates to cascade from the parent table and that sets the value to the default (0 in this case) when the parent row is deleted?

推荐答案

Oracle不支持级联更新.当删除父行时，它也不允许您将值设置为默认值. on delete行为的两个选项是cascade或set null.显然，如果希望将列声明为not null，则定义set null行为是没有意义的.

Oracle does not support cascading updates. It also does not allow you to set the value to the default when the parent row is deleted. Your two options for an on delete behavior are cascade or set null. Obviously, it wouldn't make sense to define a set null behavior if you want the column to be declared not null.

通常，主键应该是不可变的.如果打算允许更新列，通常不应该将列声明为主键.如果emp_nbr需要更改(这很奇怪)，则可能需要其他主键.如果雇员被删除，您希望雇员保留在OrgChart表中，这也让我感到奇怪.如果要允许组织结构图具有无雇员的位置，则可能希望允许emp_nbr在OrgChart中为空，并将外键声明为on delete set null.

In general, primary keys ought to be immutable. You shouldn't generally be declaring a column as a primary key if you intend to allow it to be updated. If emp_nbr needs to change (which seems odd), you probably want a different primary key. It also strikes me as odd that you'd want an employee to remain in the OrgChart table if the employee has been removed. If you want to allow an org chart to have a spot with no employee, you'd probably want to allow emp_nbr to be nullable in OrgChart and declare the foreign key to be on delete set null.

这篇关于ON DELETE SET DEFAULT-未实现的功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！