这是可能在Oracle / Sql吗？ [英] Is this possible in Oracle/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'， 'PAO'，'PLO'，'PLO'，'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）
参考地点（venue_no）
 ON DELETE SET NULL; 
  

我要设置CONSTRAINT（或TRIGGER ???），其将不允许（或标记）其中Event_details（no_players）

这是可能的吗？

• 有没有
  code> AUTOINCREMENT 关键字。您需要创建一个序列（通常每个表一个序列），并使用 INSERT NEXTVAL c>语句本身或在触发器中填充合成主键。


• 没有任何东西会创建 VENUE_NO code> EVENT_DETAILS 。我假设您的实际DDL定义该列。

您不能通过简单的 CHECK 约束。您可以创建触发器

  CREATE OR REPLACE TRIGGER validate_capacity 
 BEFORE INSERT或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，对不起，场地容量不足）; 
 END IF; 
 END; 
  

但请注意

• 您还需要在 VENUE 表中有一个触发器，以检查场地容量的更改是否会导致某些事件无效。一般来说，这将需要在事件详细信息表中有某种日期，因为假定场地的容量可能随时间而改变，并且您只想要验证以检查该场所中的未来事件。


• 基于触发器的解决方案并不总是在多用户环境中工作。想象一下会话1的容量为30.现在，会话A将该容量更新为15.但是在会话A提交之前，会话B插入一个 NO_PLAYERS 为20的事件。会话的触发器将看到一个问题，所以这两个更改都将被允许。但一旦两个会议提交，将有一个事件预订了20名球员在一个场地，只支持15个球员。 EVENT_DETAILS 上的触发器可能锁定 VENUE 表中的行，以避免此竞争条件，但是它们正在序列化插入和更新 EVENT_DETAILS 表，这可能是一个性能问题，特别是如果您的应用程序在提交事务之前等待人工输入。






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




I have two tables:

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));

And a foreign key between them:

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

I want to set up a CONSTRAINT (or TRIGGER???) that will not allow (or flag) an entry where Event_details(no_players) < Venue(capacity) where the Venue(capacity) is the value found in the foreign key row.

Is this possible?

解决方案

A couple of comments on the DDL you posted.

• There is no AUTOINCREMENT keyword in Oracle. You'd need to create a sequence (generally one sequence per table) and use the NEXTVAL from the sequence either in the INSERT statement itself or in a trigger to populate the synthetic primary key.
• There is nothing that is creating a VENUE_NO column in EVENT_DETAILS. I assume your actual DDL is defining that column.

You cannot enforce this through a simple CHECK constraint. You can create a trigger

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;

Be aware, however, that

• You would also need to have a trigger on the VENUE table that checks to see whether changes to the venue's capacity causes certain events to become invalid. Generally, that would require that there is some sort of date in the event details table since, presumably, the capacity of a venue can change over time and you really only want the validation to check for future events in that venue.
• Trigger based solutions will not always work in multi-user environments. Imagine venue 1 has a capacity of 30. Now, session A updates that capacity to 15. But before session A commits, session B inserts an event with a NO_PLAYERS of 20. Neither session's trigger will see a problem so both changes will be allowed. But once both sessions commit, there will be an event booked with 20 players in a venue that only supports 15 players. The trigger on EVENT_DETAILS could potentially lock the row in the VENUE table to avoid this race condition but they you're serializing inserts and updates on the EVENT_DETAILS table which could be a performance problem particularly if your application ever waits for human input before committing a transaction.

As an alternative to triggers, you can create an ON COMMIT materialized view that joins the two tables together and put a CHECK constraint on that materialized view that enforces the requirement that the number of players cannot exceed the venue capacity. That will work in a multi-user environment but it requires materialized view logs on both base tables and it moves the check to the point where the sessions commit which can be a bit tricky. Most applications don't consider the possibility that a COMMIT statement could fail so handling those exceptions can be tricky. And from a user-interface standpoint, it can be somewhat tricky to explain to the user what the problem is since the exception may relate to changes made much earlier in the transaction.

这篇关于这是可能在Oracle / Sql吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！