Oracle 向默认为0的整数属性添加约束

嗨，汤姆，

我有两个表，包含我的主键x的表A和引用表B的属性y的表B，但是直到现在我还没有添加约束。
因此，

A -> x
B ->y

和y参考x。

我现在需要在表B上添加一个约束y referencing x

ALTER表 'B' 添加约束fk_B_A外键 ('y') 引用A('x');

我无法添加此约束，因为表A有一些记录，其中y列保存值0，因为它具有默认约束为NULL，但其整数类型。由于此值0不存在于列x中，因此不允许我应用该约束。

我怎么能找到解决这个问题的工作，因为我需要我的表B中有y值为0的记录，如果它保存为NULL，它会工作得很好，我手动更新了记录以设置y = NULL，并添加了约束。

但是对于我未来的记录，如果我创建一个记录，期望在表B中更新y的默认值 (这将保存为0)，它将检查它与表a中的x并没有找到它，并且不允许我们创建记录。

这是正确的吗？

非常感谢您可以让我知道我们如何解决此问题。

非常感谢您的时间，
菲奥娜

这里有几个选项，你可以看看

1) 添加触发器以将零转置为null
2) 添加一个虚拟列，以便维护您的零，但仍可以尽可能地执行完整性约束

SQL> create table a ( x int primary key );

Table created.

SQL> create table b ( z int, y int default 0 references a(x));

Table created.

SQL>
SQL> insert into a values (1);

1 row created.

SQL> insert into b ( z ) values ( 123 );
insert into b ( z ) values ( 123 )
*
ERROR at line 1:
ORA-02291: integrity constraint (MCDONAC.SYS_C0013120) violated - parent key not found


SQL>
SQL> --
SQL> -- Option 1
SQL> --
SQL> create or replace
  2  trigger mangle_b
  3  before insert or update on b
  4  for each row
  5  begin
  6    :new.y := case when :new.y != 0 then :new.y end;
  7  end;
  8  /

Trigger created.

SQL>
SQL> insert into b ( z ) values ( 123 );

1 row created.

SQL> select * from b;

         Z          Y
---------- ----------
       123

1 row selected.

SQL>
SQL> --
SQL> -- Option 2
SQL> --
SQL>
SQL> drop table b purge;

Table dropped.

SQL> create table b ( z int, y int default 0);

Table created.

SQL>
SQL> alter table b add custom_y int generated always as ( case when y != 0 then y end );

Table altered.

SQL>
SQL> alter table b add constraint custom_y_fk
  2  foreign key ( custom_y) references a(x);

Table altered.

SQL>
SQL> insert into b ( z ) values ( 123 );

1 row created.

SQL> select * from b;

         Z          Y   CUSTOM_Y
---------- ---------- ----------
       123          0

1 row selected.

SQL>
SQL> insert into b ( z,y ) values (1,1  );

1 row created.

SQL> select * from b;

         Z          Y   CUSTOM_Y
---------- ---------- ----------
       123          0
         1          1          1

2 rows selected.

SQL>
SQL> insert into b ( z,y ) values (1,2  );
insert into b ( z,y ) values (1,2  )
*
ERROR at line 1:
ORA-02291: integrity constraint (MCDONAC.CUSTOM_Y_FK) violated - parent key not found


SQL>