如何向Oracle表添加约束以确保表中的每一行至少在另一表中被引用？

huangapple
go评论79阅读模式
英文:

How can I add a constraint to an Oracle table to make sure, each row in the table is referenced at least once in another table?

问题

在我的Oracle数据库中，我有一个名为A的表，其中的ID列在表B中被引用。然而，目前，并非A的每一行都在B中被引用。我想要在A表中添加一种约束或虚拟列，确保A中的每一行至少在表B中有一行，换句话说，A表的每一行都至少被B表的一行引用。但是我不知道该如何做。我考虑过在A表中创建一个可延迟的虚拟列，就像这里的伪代码：

  1. alter table A add (b_id as (
  2.     case select id into b_id from B中引用A的第一行
  3.         END) unique deferrable initially deferred);

但是这对我来说没有意义，而且我甚至不确定是否可能在表的虚拟列中引用另一个表。作为一个新手，我觉得可能有更好的选择。你会怎么做？有什么想法吗？

英文:

In my oracle DB i have table A that is referenced through its ID column in table B. Though, currently, not every row of A is referenced in B. I would like to add a contraint of sorts or a virtual column to A that ensures that for EVERY row in A there is at least one row in table B or, in other words, every table A row is referenced by at least one row in table B. But i don't know how to go about it. i thought about doing a deferrable virtual column in table A like in this pseudo code here:

  1. alter table A add (b_id as (
  2.     case select id into b_id from the first row of B that references A
  3.         END) unique deferrable initially deferred);

but this doesn't make sense to me and, i'm not even sure if it is at all possible to reference another table in the virtual column of a table. i feel like, as a newbie, there might be some better options out there. how would you go about it, any ideas?

答案1

得分: 3

以下是翻译好的部分：

如果您想要建立一个父子关系，其中每个父项都至少有一个子项，那么您可以从父项创建一个可延迟的外键链接到子项：

  1. create table employees ( 
  2.   employee_id   int primary key,
  3.   first_name    varchar2(100) not null, 
  4.   last_name     varchar2(100) not null, 
  5.   department_id int not null
  6. );
  7.   
  8. create table departments ( 
  9.   department_id   int primary key, 
  10.   department_name varchar2(30) not null, 
  11.   manager_id      int not null
  12. );
  14. alter table employees
  15.   add constraint empl_department_fk
  16.   foreign key ( department_id )
  17.   references departments;
  18.   
  19. alter table departments
  20.   add constraint dept_manager_fk
  21.   foreign key ( manager_id )
  22.   references employees ( employee_id )
  23.   deferrable;
  25. /* 延迟可延迟的约束验证 */
  26. alter session set constraints = deferred;
  28. insert into departments ( department_id, department_name, manager_id )
  29. values ( 42, 'Test dept', 42 );
  31. /* 在此处验证约束 - 引发错误并回滚 */
  32. commit;
  33. --ORA-02291: integrity constraint (CHRIS.DEPT_MANAGER_FK) violated - parent key not found
  35. insert into departments ( department_id, department_name, manager_id )
  36. values ( 42, 'Test dept', 42 );
  38. insert into employees ( employee_id, first_name, last_name, department_id )
  39. values ( 42, 'Tess', 'Ting', 42 );
  41. commit;

您可以在每个父行都有来自子行的默认/主要/所有者行时使用此方法。这比通常假设的要常见 - 例如，每个部门必须有一个经理，每个客户必须有一个默认的付款方法，每个人必须有一个主要联系方法。

如果没有默认/主要/所有者，您可以通过创建一个查询外连接子项到父项的“在提交时快速刷新”的物化视图来强制执行此操作。在MV中的子列上添加（可延迟的）非空约束：

  1. create materialized view log on employees
  2.   with rowid, primary key ( department_id ),
  3.   sequence
  4.   including new values;
  5.   
  6. create materialized view log on departments
  7.   with rowid, primary key, 
  8.   sequence
  9.   including new values;
  11. /* 
  12.   是否有没有员工的部门？
  13.   MV将子项与父项外连接 
  14. */
  15. create materialized view department_employees_mv
  16.   refresh fast on commit
  17. as
  18.   select e.rowid empl_rid, d.rowid dept_rid,
  19.          e.employee_id, d.department_id
  20.   from   employees e, departments d
  21.   where  d.department_id = e.department_id (+);
  23. /* 检查子列不为空 */
  24. alter table department_employees_mv 
  25.   modify employee_id 
  26.     constraint deem_employee_nn
  27.     not null
  28.     deferrable;
  29.     
  30. /* 证明MV有效 - 删除部门 -> 员工FK */
  31. alter table departments
  32.   drop constraint dept_manager_fk;
  34. /* 添加没有员工的新部门 */
  35. insert into departments ( department_id, department_name, manager_id )
  36. values ( 99, 'test', 99 );
  38. /* MV查询 - 请注意employee_id为空 */
  39. select e.employee_id, d.department_id
  40. from   employees e, departments d
  41. where  d.department_id = e.department_id (+)
  42. and    d.department_id = 99;
  44. commit;
  45. --ORA-02091: transaction rolled back
  46. --ORA-02290: check constraint (CHRIS.DEEM_EMPLOYEE_NN) violated
  48. /* 插入被回滚 */
  49. select e.employee_id, d.department_id
  50. from   employees e, departments d
  51. where  d.department_id = e.department_id (+)
  52. and    d.department_id = 99;
  54. insert into departments ( department_id, department_name, manager_id )
  55. values ( 99, 'test', 99 );
  57. insert into employees ( employee_id, first_name, last_name, department_id )
  58. values ( 99, 'Tess', 'Ting', 99 );
  60. commit;
  62. select * from department_employees_mv
  63. where  department_id = 99;
  65. EMPL_RID           DEPT_RID           EMPLOYEE_ID DEPARTMENT_ID
  66. ------------------ ------------------ ----------- -------------
  67. AAAToCAAPAAAANHAAB AAAToEAAPAAAAMPAAB          99            99

有关更多信息，请参阅我最近在高级数据库约束上的Ask TOM Office Hours。

英文:

If you want a parent-child relational where every parent has (at least) one child then you can create a deferrable foreign key from the parent back to the child:

  1. create table employees ( 
  2. employee_id   int primary key,
  3. first_name    varchar2(100) not null, 
  4. last_name     varchar2(100) not null, 
  5. department_id int not null
  6. );
  7. create table departments ( 
  8. department_id   int primary key, 
  9. department_name varchar2(30) not null, 
  10. manager_id      int not null
  11. );
  12. alter table employees
  13. add constraint empl_department_fk
  14. foreign key ( department_id )
  15. references departments;
  16. alter table departments
  17. add constraint dept_manager_fk
  18. foreign key ( manager_id )
  19. references employees ( employee_id )
  20. deferrable;
  21. /* Delay deferrable constraint validation */
  22. alter session set constraints = deferred;
  23. insert into departments ( department_id, department_name, manager_id )
  24. values ( 42, 'Test dept', 42 );
  25. /* Constraint is validated here - raise error & rollback */
  26. commit;
  27. --ORA-02291: integrity constraint (CHRIS.DEPT_MANAGER_FK) violated - parent key not found
  28. insert into departments ( department_id, department_name, manager_id )
  29. values ( 42, 'Test dept', 42 );
  30. insert into employees ( employee_id, first_name, last_name, department_id )
  31. values ( 42, 'Tess', 'Ting', 42 );
  32. commit;

You can use this whenever each parent row has a default/primary/owner row from the child rows. This is more common than often assumed - e.g. Every department must have a manager, every customer must have a default payment method, every person must have a primary contact method.

If there is no default/primary/owner, you can enforce this by creating a fast refresh on commit materialized view with a query outer joining the child to the parent. Add a (deferrable) not null constraint over child columns in the MV and voila:

  1. create materialized view log on employees
  2. with rowid, primary key ( department_id ),
  3. sequence
  4. including new values;
  5. create materialized view log on departments
  6. with rowid, primary key, 
  7. sequence
  8. including new values;
  9. /* 
  10. Is there a dept with no employees?
  11. MV outer joining child to parent 
  12. */
  13. create materialized view department_employees_mv
  14. refresh fast on commit
  15. as
  16. select e.rowid empl_rid, d.rowid dept_rid,
  17. e.employee_id, d.department_id
  18. from   employees e, departments d
  19. where  d.department_id = e.department_id (+);
  20. /* Check child columns are not null */
  21. alter table department_employees_mv 
  22. modify employee_id 
  23. constraint deem_employee_nn
  24. not null
  25. deferrable;
  26. /* Prove MV works - drop dept -> emp FK */
  27. alter table departments
  28. drop constraint dept_manager_fk;
  29. /* Add new dept with no employees */
  30. insert into departments ( department_id, department_name, manager_id )
  31. values ( 99, 'test', 99 );
  32. /* MV query - note employee_id is null */
  33. select e.employee_id, d.department_id
  34. from   employees e, departments d
  35. where  d.department_id = e.department_id (+)
  36. and    d.department_id = 99;
  37. EMPLOYEE_ID DEPARTMENT_ID
  38. ----------- -------------
  39. <null>                 99
  40. commit;
  41. --ORA-02091: transaction rolled back
  42. --ORA-02290: check constraint (CHRIS.DEEM_EMPLOYEE_NN) violated
  43. /* Insert rolled back */
  44. select e.employee_id, d.department_id
  45. from   employees e, departments d
  46. where  d.department_id = e.department_id (+)
  47. and    d.department_id = 99;
  48. insert into departments ( department_id, department_name, manager_id )
  49. values ( 99, 'test', 99 );
  50. insert into employees ( employee_id, first_name, last_name, department_id )
  51. values ( 99, 'Tess', 'Ting', 99 );
  52. commit;
  53. select * from department_employees_mv
  54. where  department_id = 99;
  55. EMPL_RID           DEPT_RID           EMPLOYEE_ID DEPARTMENT_ID
  56. ------------------ ------------------ ----------- -------------
  57. AAAToCAAPAAAANHAAB AAAToEAAPAAAAMPAAB          99            99

For more on this see my recent Ask TOM Office Hours on Advanced Database Constraints.

huangapple
  • 本文由 发表于 2023年5月22日 22:09:21
  • 转载请务必保留本文链接：https://go.coder-hub.com/76307061.html
  • constraints
  • oracle
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定