在Oracle / Sql中这可能吗？

小编典典

在Oracle / Sql中这可能吗？

sql

我有两个表：

CREATE TABLE Event_details( event_no INTEGER AUTOINCREMENT NOT NULL,
no_players INTEGER NOT NULL,
game_type VARCHAR(20) NOT NULL,
payout_positions INTEGER NOT NULL, 
PRIMARY KEY(event_no)
CONSTRAINT check_game_type CHECK(game_type IN ('NLH','NLO','PLO','PLH','STUD','HORSE')
CONSTRAINT check_no_players CHECK (no_players > 1)
CONSTRAINT check_payouts CHECK (payout_positions > 0 AND payout_positions < no_players));

CREATE TABLE Venue( venue_no INTEGER AUTOINCREMENT NOT NULL,
name VARCHAR(20) NOT NULL,
location VARCHAR(20) NOT NULL,
capacity INTEGER NOT NULL,
PRIMARY KEY (venue_no)
CONSTRAINT check_capacity CHECK (capacity > 0));

以及它们之间的外键：

ALTER TABLE Event_details
ADD FOREIGN KEY (venue_no)
REFERENCES Venue(venue_no)
ON DELETE SET NULL;

我想设置一个CONSTRAINT（或TRIGGER
???），该条目将不允许（或标记）Event_details（no_players）<Venue（capacity）其中Venue（capacity）是在外键行中找到的值的条目。

这可能吗？

阅读 190

2021-05-16

共1个答案

小编典典

关于您发布的DDL的一些评论。

AUTOINCREMENTOracle中没有关键字。您需要创建一个序列（通常每个表一个序列），并NEXTVAL在INSERT语句本身或触发器中使用来自序列的from来填充合成主键。
没有任何内容可在中创建VENUE_NO列EVENT_DETAILS。我假设您的实际DDL正在定义该列。

您不能通过简单的CHECK约束来强制执行此操作。您可以创建一个触发器

CREATE OR REPLACE TRIGGER validate_capacity
  BEFORE INSERT OR UPDATE ON event_details
  FOR EACH ROW
DECLARE
  l_venue_capacity venue.capacity%type;
BEGIN
  SELECT capacity
    INTO l_venue_capacity
    FROM venue
   WHERE venue_no = :new.venue_no;

  IF( l_venue_capacity < :new.no_players )
  THEN
    RAISE_APPLICATION_ERROR( -20001, 'Sorry, the venue has insufficient capacity' );
  END IF;
END;

但是请注意，

您还需要在VENUE表上有一个触发器，以检查场地容量的变化是否导致某些事件变为无效。通常，这将需要事件详细信息表中有某种日期，因为假定场地的容量可能会随时间而变化，并且您实际上只希望验证来检查该场地中将来的事件。
基于触发器的解决方案并不总是在多用户环境中工作。想象一下，场所1的容量为30。现在，会话A将其容量更新为15。但是在会话A提交之前，会话B插入了事件A，其NO_PLAYERS值为20。这两个会话的触发器都不会出现问题，因此将允许进行两个更改。但是，一旦两次会议都提交，将在只支持15名玩家的场地上预订20名玩家参加的活动。触发器EVENT_DETAILS可能会锁定VENUE表中的行以避免这种竞争情况，但是您正在序列化EVENT_DETAILS表上的插入和更新，这可能是性能问题，尤其是如果您的应用程序在提交事务之前曾等待人工输入时。

作为触发器的替代方法，您可以创建ON COMMIT将两个表连接在一起的CHECK实例化视图，并对该实例化视图施加约束，以强制要求玩家人数不能超过场地容量。这将在多用户环境中工作，但是它需要两个基表上的物化视图日志，并将检查移到会话提交的位置，这可能会有些棘手。大多数应用程序不考虑COMMIT语句可能失败的可能性，因此处理这些异常可能很棘手。并且从用户界面的角度来看，向用户解释问题是有些棘手的，因为异常可能与事务中更早进行的更改有关。

2021-05-16