在預訂系統中維護參考完整性

我正在 Microsoft SQL Server 2005 上開發企業培訓預訂系統的第二個版本。

我有 3 個表（針對這個問題進行了簡化）。

表 1 - CourseSize 表 - 這決定了能夠預訂課程的最大參與者數量。

表 2 - 課程 - 要提供的課程、日期/時間和對 CourseSize 記錄的引用。

表 3 - 預訂表 - 參考相關會話記錄進行預訂的人。

SQL如下 -

CREATE TABLE [dbo].[CourseSize]
(
   [ID] [int] IDENTITY(1,1) NOT NULL,
   [CourseSizeMax] [tinyint] NOT NULL,
   CONSTRAINT [PK_CourseSize] PRIMARY KEY CLUSTERED ([ID] ASC)
);

CREATE TABLE [dbo].[Session]
(
   [ID] [int] IDENTITY(1,1) NOT NULL,
   [CourseSizeID] [int] NOT NULL,
   CONSTRAINT [PK_Session] PRIMARY KEY CLUSTERED ([ID] ASC)
);
GO

ALTER TABLE [dbo].[Session] WITH CHECK 
ADD CONSTRAINT [FK_Session_CourseSize] 
FOREIGN KEY([CourseSizeID])
REFERENCES [dbo].[CourseSize] ([ID]);
GO

ALTER TABLE [dbo].[Session] 
CHECK CONSTRAINT [FK_Session_CourseSize];
GO

CREATE TABLE [dbo].[Booking]
(
   [ID] [int] IDENTITY(1,1) NOT NULL,
   [SessionID] [int] NOT NULL,
   CONSTRAINT [PK_Booking] PRIMARY KEY CLUSTERED ([ID] ASC)
);
GO

ALTER TABLE [dbo].[Booking] WITH CHECK
ADD CONSTRAINT [FK_Booking_Session] 
FOREIGN KEY([SessionID])
REFERENCES [dbo].[Session] ([ID]);
GO

ALTER TABLE [dbo].[Booking] 
CHECK CONSTRAINT [FK_Booking_Session];
GO

問題- 如果針對會話的預訂數量已達到最大課程規模，我需要確保停止插入到預訂表中。在過去，我使用過這樣的東西：

INSERT INTO Booking
SELECT 1 AS SessionID    
WHERE 
   (
       SELECT COUNT(*) 
       FROM Booking 
       WHERE SessionID = 1
   ) <= 
   (
       SELECT CourseSizeMax 
       FROM CourseSize 
           INNER JOIN Session ON CourseSize.ID = Session.CourseSizeID 
       WHERE Session.ID = 1
   );

當多個使用者同時進行預訂時，此操作會失敗。

我通過將 CourseSizeMax 設置為 2 並使用WAITFOR time. 我安排查詢執行 3 次；2 個同時和其他兩個之前的最後 1 毫秒。他們都能夠插入一行，從而超過了會話大小限制。

我真的很想避免使用觸發器來處理這個；我的偏好是使用約束。我完全控制了數據庫的設計，因此如果需要更改結構沒有問題。

如何防止同時更新打破課程規模限制？

如果你只有兩張桌子，Session 和 Booking，你可以這樣做：

1. 將 CourseSizeMax 列添加到 dbo.Session 表中，並在$$ dbo $$.$$ Session $$($$ ID $$,$$ CourseSizeMax $$) - 稍後需要。
2. 將 CourseSizeMax 列和 BookingNumber 列添加到 dbo.Booking 表中。
3. 在 dbo.Booking(CourseId, CourseSizeMax) 上添加 FK 約束，引用 Session
4. 添加支票（BookingNumber BETWEEN 1 AND CourseSizeMax）
5. 在 dbo.Booking(CourseId, BookingNumber) 上添加 UNIQUE 約束

只要您的所有約束都受信任，您就一切就緒。

我不確定你為什麼需要第三張表 CourseSize。

您要求的是跨 2 個表的約束或具有涉及另一個表的子查詢的表級約束（這是 SQL-Server 表級約束的限制）。檢查CREATE TABLE語法，段落檢查約束：

列級 CHECK 約束只能引用受約束的列，而表級 CHECK 約束只能引用同一表中的列。

在 SQL-92 標準中，有ASSERTION（超過 1 個表的約束），如果它可用，這實際上是您將使用的。請參閱此問題中的答案：為什麼 DBMS 不支持 ASSERTION以獲取詳細資訊以及有關具有此類功能但有限制的某些產品（MS-Access）的資訊。

Firebird 文件說它允許 CHECK 約束中的子查詢。

恐怕在 SQL-Server 中，您唯一的解決方案就是觸發器。

引用自：https://dba.stackexchange.com/questions/15757