关于PK可引用多少次的SQL set约束

Question

我正在为我的学校项目建立一个动物园演示数据库，我遇到了以下问题:我有一个桌子展馆，它有一些主键id_pavilion和列容量(这是关于在这个展馆中生活的动物数量最多的信息)。

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

亭

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   

(这显示了我想要阻止的事情)

然后我有了餐桌动物，其中包含了一些有关动物的信息，主要是作为外键来自展馆的id_pavilion。

，我的问题是：我如何添加这样一个约束，使Pavilion的PK id_pavilion在容量允许的情况下只能在表Animal中被引用很多次？

Answer 1

从你的例子数据可以看出，每个展馆可以容纳两只动物，对吗？人们还可以说，在以适当的方式饲养动物之前，“食宿”必须就位。因此，我们可以创建一个名为could的表，列出所有可用的空间。

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.

每只动物都应该在一个单一的(定义的)位置。当一只动物被“添加”到动物园时，它只能(在身体上)在一个单一的地点/住所。我们可以使用唯一的键和外键(参考住宿)来执行此操作。

-- 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 这里.用甲骨文12c和18c进行测试。(您需要12c+版本才能使横向连接工作。)

Answer 2

您试图在数据库级别强制执行的是“业务逻辑”规则，而不是硬数据约束。您不能在表设计中直接实现它；即使您可以(正如@serg在注释中提到的那样)，它也需要一个非常昂贵的锁(就CPU/资源而言)来执行计数。

实现目标并将业务逻辑与数据设计分开的另一个选择是使用SQL触发器。

触发器可以在数据插入到您的表之前运行；在这里，您可以检查已经为该“临时实体”插入了多少行，并中止或允许插入。

关于“学校项目”方面的评论:话虽如此，你谈论的那种逻辑在你的消费应用程序中要比在数据库中更好(我的观点是，其他人可能不同意)。还可以考虑在数据中定义大小限制，这样就可以有不同大小的展馆了。

注:对于将来访问此问题的任何人，上面的链接都是针对甲骨文触发器的( OP为oracle标记了问题)。T他的链接是用于触发器的。

Answer 3

答案是“不容易”。虽然把“食宿”放在亭子里作为一个单独的桌子是一个聪明的想法，动物是放在展馆，而不是住宿。模特儿的适应使得动物的移动变得更加困难。

也许最简单的方法是使用触发器。这从pavilions中的一个pavilions列开始。该列从零开始，并随着动物的进进出出而递增或减少。您可以使用check约束来验证展馆的容量是否过大。

不幸的是，维护这个列需要animals表上的触发器，其中一个用于insert、update和delete。

最后，触发器是保持计数，如果你试图把一个动物放在一个完整的展馆，你将违反check约束。