SQL设置限制一个PK可以被引用多少次

Question

我正在为我的学校项目构建一个动物园的演示数据库，我遇到了以下问题:我有一个表 Pavilion，它有一些主键 id_pavilion 和列容量(这是关于最大数量的信息可以住在这个展馆里的动物)。

假设每个展馆最多可以容纳 2 只动物。

展馆

id_pavilion   capacity
-----------------------
    1             2
    2             2
    3             2
    4             2

动物

id_an-column2-column3    id_pavilion
---------------------------------------
   1                          2   
   2                          2   
   3                          2   
   4                          2   

(这表明我正在努力阻止)

然后我有 table animal，它包含一些关于动物的信息，主要是来自 Pavilion 的 id_pavilion 作为外键。

我的问题是:如何添加这样的约束，使 Pavilion 中的 PK id_pavilion 可以在表 Animal 中引用 容量允许多少次？

Answer 1

查看您的示例数据，有人可能会争辩说每个 PAVILION 可以容纳 2 只动物，对吗？也可以说，“住宿”需要到位，才能以适当的方式饲养动物。因此，我们可以创建一个名为 ACCOMMODATION 的表，列出所有可用空间。

create table pavilion( id primary key, capacity )
as
select level, 2 from dual connect by level <= 4 ;

create table accommodation(
  id number generated always as identity start with 1000 primary key 
, pavilionid number references pavilion( id )
) ;

生成所有住宿

-- No "human intervention" here.  
-- Only the available spaces will be INSERTed.
insert into accommodation ( pavilionid )
select id
from pavilion P1, lateral ( 
  select 1 
  from dual 
  connect by level <= ( select capacity from pavilion where id = P1.id )
) ;

-- we can accommodate 8 animals ...
select count(*) from accommodation ; 

  COUNT(*)
----------
         8

-- accommodations and pavilions
SQL> select * from accommodation ;

        ID PAVILIONID
---------- ----------
      1000          1
      1001          1
      1002          2
      1003          2
      1004          3
      1005          3
      1006          4
      1007          4

8 rows selected.

每只动物都应该在一个(定义的)位置。当动物被“添加”到动物园时，它只能(物理上)在一个位置/住宿。我们可以使用 UNIQUE 键和 FOREIGN 键(引用 ACCOMMODATION)来执行此操作。

-- the ANIMAL table will have more columns eg GENUS, SPECIES, NAME etc
create table animal( 
  id number generated always as identity start with 2000
-- , name varchar2( 64 ) 
, accommodation number 
) ;

alter table animal
add (
  constraint animal_pk primary key( id )
, constraint accommodation_unique unique( accommodation )
, constraint accommodation_fk 
    foreign key( accommodation ) references accommodation( id )
);

测试

-- INSERTs will also affect the columns GENUS, SPECIES, NAME etc
-- when the final version of the ANIMAL table is in place. 
insert into animal( accommodation ) values ( 1001 )  ;

SQL> insert into animal( accommodation ) values ( 1000 )  ;

1 row inserted.

SQL> insert into animal( accommodation ) values ( 1001 )  ;

1 row inserted.

-- trying to INSERT into the same location again 
-- MUST fail (due to the unique constraint)
SQL> insert into animal( accommodation ) values ( 1000 );
Error starting at line : 1 in command -
insert into animal( accommodation ) values ( 1000 )
Error report -
ORA-00001: unique constraint (...ACCOMMODATION_UNIQUE) violated


SQL> insert into animal( accommodation ) values ( 1001 );
Error starting at line : 1 in command -
insert into animal( accommodation ) values ( 1001 )
Error report -
ORA-00001: unique constraint (...ACCOMMODATION_UNIQUE) violated

-- trying to INSERT into a location that does not exist
-- MUST fail (due to the foreign key constraint)
SQL> insert into animal( accommodation ) values ( 9999 )  ;
Error starting at line : 1 in command -
insert into animal( accommodation ) values ( 9999 )
Error report -
ORA-02291: integrity constraint (...ACCOMMODATION_FK) violated - parent key not found

动物和住宿

select 
  A.id as animal
, P.id as pavilion
, AC.id as location --(accommodation)
from pavilion P
  join accommodation AC on P.id = AC.pavilionid
  join animal A on AC.id = A.accommodation
;

    ANIMAL   PAVILION   LOCATION
---------- ---------- ----------
      2000          1       1000
      2001          1       1001

DBfiddle here .使用 Oracle 12c 和 18c 测试。 (要使 LATERAL join 工作，您需要 12c+ 版本。)