sql—在oracle中删除一个附加了外键的主键约束,然后重新启用主键时会发生什么情况?

dzhpxtsq  于 2021-07-24  发布在  Java
关注(0)|答案(2)|浏览(353)

好吧,假设你有两张table, Table1 以及 Table2 . Table1 的主键是的外键 Table2 . 如果将主键约束放在 Table1 然后用 Alter Table 命令,表2上的外键是否也会自动重新启用?

0g0grzrc

0g0grzrc1#

让我们试试会发生什么。
首先创建表t1和t2。

  1. CREATE TABLE t1 (parent_id NUMBER);
  2. Table created
  3. CREATE TABLE t2 (child_id NUMBER);
  4. Table created

然后创建两个约束

  1. ALTER TABLE t1 ADD CONSTRAINT t1_pk PRIMARY KEY (parent_id) USING INDEX;
  2. Table altered
  3. ALTER TABLE t2 ADD CONSTRAINT t2_fk FOREIGN KEY (child_id) REFERENCES t1;
  4. Table altered

现在查看字典并选择这两个表的约束

  1. SELECT c.CONSTRAINT_NAME, c.CONSTRAINT_TYPE, c.R_CONSTRAINT_NAME
  2. FROM User_Constraints c WHERE c.TABLE_NAME IN ('T1', 'T2');
  3. CONSTRAINT_NAME CONSTRAINT_TYPE R_CONSTRAINT_NAME
  4. ------------------------------ --------------- ------------------------------
  5. T1_PK P
  6. T2_FK R T1_PK

我们看到1个主键(p)和1个外键(r),然后删除pk

  1. ALTER TABLE t1 DROP CONSTRAINT t1_pk CASCADE;
  2. Table altered

cascade关键字指示oracle隐式删除所有引用要删除的pk的fk约束。如果没有cascade,您将得到ora-02273:这个唯一/主键被一些外键引用,pk不会被删除。
再次查看字典并选择这两个表的约束

  1. SELECT c.CONSTRAINT_NAME, c.CONSTRAINT_TYPE, c.R_CONSTRAINT_NAME
  2. FROM User_Constraints c WHERE c.TABLE_NAME IN ('T1', 'T2');
  3. CONSTRAINT_NAME CONSTRAINT_TYPE R_CONSTRAINT_NAME
  4. ------------------------------ --------------- ------------------------------

没有行-pk和fk都没有了。重新创建pk

  1. ALTER TABLE t1 ADD CONSTRAINT t1_pk PRIMARY KEY (parent_id) USING INDEX;
  2. Table altered

再看看字典

  1. SELECT c.CONSTRAINT_NAME, c.CONSTRAINT_TYPE, c.R_CONSTRAINT_NAME
  2. FROM User_Constraints c WHERE c.TABLE_NAME IN ('T1', 'T2');
  3. CONSTRAINT_NAME CONSTRAINT_TYPE R_CONSTRAINT_NAME
  4. ------------------------------ --------------- ------------------------------
  5. T1_PK P

只创建pk。清理测试用例

  1. DROP TABLE t2;
  2. Table dropped
  3. DROP TABLE t1;
  4. Table dropped
展开查看全部
t0ybt7op

t0ybt7op2#

让我们做一个你所要求的测试用例。我希望我正确理解了前提:
我有一个表myu pk,在c1列上有一个主键
我有一个表myfk,其中包含列c1上的主键,它也是表mypk中列c1的外键。
您想禁用或删除表my\u fk中的主键,以查看外键的情况。
我们来玩:

  1. SQL> create table my_pk ( c1 number primary key , c2 number );
  2. Table created.
  3. SQL> create table my_fk ( c1 number primary key , c2 number, constraint my_fk_constraint foreign key (c1) references my_pk (c1) ) ;
  4. Table created.
  5. SQL> select table_name, constraint_name, constraint_type, status from dba_constraints where table_name in ( 'MY_FK' , 'MY_PK' ) and owner = 'MYOWNER' ;
  6. TABLE_NAME CONSTRAINT_NAME C STATUS
  7. ------------------------------ ------------------------------ - --------
  8. MY_PK SYS_C00141013 P ENABLED
  9. MY_FK SYS_C00141016 P ENABLED
  10. MY_FK MY_FK_CONSTRAINT R ENABLED

那么,让我们照你说的做:
禁用表my\u fk中的主键,它也是表my\u pk的外键

  1. SQL> alter table my_fk disable primary key ;
  2. Table altered.
  3. SQL> select table_name, constraint_name, constraint_type, status from dba_constraints where table_name in ( 'MY_FK' , 'MY_PK' ) and owner = 'MY_OWNER';
  4. TABLE_NAME CONSTRAINT_NAME C STATUS
  5. ------------------------------ ------------------------------ - --------
  6. MY_PK SYS_C00141013 P ENABLED
  7. MY_FK SYS_C00141016 P DISABLED
  8. MY_FK MY_FK_CONSTRAINT R ENABLED

让我们评估外键是否仍然被强制执行,即使pk被禁用。您可以在上面看到,fk在字典中显示为已启用。

  1. SQL> insert into my_pk values ( 1 , 1 );
  2. 1 row created.
  3. SQL> commit;
  4. Commit complete.
  5. SQL> insert into my_fk values ( 2, 1 );
  6. insert into my_fk values ( 2, 1 )
  7. *
  8. ERROR at line 1:
  9. ORA-02291: integrity constraint (MYOWNER.MY_FK_CONSTRAINT) violated - parent
  10. key not found

外键仍然是强制的,尽管pk是禁用的,如果需要,您仍然可以插入重复项

  1. SQL> insert into my_fk values ( 1 , 1 );
  2. 1 row created.
  3. SQL> rollback;
  4. Rollback complete.
  5. SQL>

我们重新启用表my\u fk中的pk

  1. SQL> alter table my_fk enable primary key ;
  2. Table altered.
  3. SQL> select table_name, constraint_name, constraint_type, status from dba_constraints where table_name in ( 'MY_FK' , 'MY_PK' ) ;
  4. TABLE_NAME CONSTRAINT_NAME C STATUS
  5. ------------------------------ ------------------------------ - --------
  6. MY_PK SYS_C00141013 P ENABLED
  7. MY_FK SYS_C00141016 P ENABLED
  8. MY_FK MY_FK_CONSTRAINT R ENABLED

删除表my\u fk中的主键,它也是my\u pk的外键

  1. SQL> alter table my_fk drop primary key ;
  2. Table altered.
  3. SQL> select table_name, constraint_name, constraint_type, status from dba_constraints
  4. where table_name in ( 'MY_FK' , 'MY_PK' ) ;
  5. TABLE_NAME CONSTRAINT_NAME C STATUS
  6. ------------------------------ ------------------------------ - --------
  7. MY_PK SYS_C00141013 P ENABLED
  8. MY_FK MY_FK_CONSTRAINT R ENABLED
  9. SQL> insert into my_pk values ( 2 , 2 );
  10. 1 row created.
  11. SQL> select * from my_pk ;
  12. C1 C2
  13. ---------- ----------
  14. 1 1
  15. 2 2
  16. SQL> insert into my_fk values ( 2 ,2 );
  17. 1 row created.
  18. SQL> select * from my_fk ;
  19. C1 C2
  20. ---------- ----------
  21. 1 1
  22. 2 2
  23. SQL> insert into my_fk values ( 3 , 3 );
  24. insert into my_fk values ( 3 , 3 )
  25. *
  26. ERROR at line 1:
  27. ORA-02291: integrity constraint (MYOWNER.MY_FK_CONSTRAINT) violated - parent
  28. key not found

因此,无论是禁用主键约束还是在表my\u fk中删除主键约束,外键仍然是强制的。显然,您可以在禁用主键的同时向表中添加重复项。在我的测试用例中,我没有使用cascade选项,它改变了另一个答案中提供的场景。

展开查看全部

相关问题