Maintaining referential integrity in a booking system I am developing a second version of a corporate training booking system on Microsoft SQL Server 2005.

I have 3 tables (simplified for this question). 

Table 1- CourseSize table- this determines the maximum number of participants able to book into the session.
Table 2- Session- the course to be provided, the date/time and a reference to CourseSize record.
Table 3- Booking table- the person who made the booking with reference to the relevant Session record.  

The SQL is as follows-

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


Problem- I need to ensure that inserts into the Booking table are stopped if the number of bookings against a session has reached the maximum course size.  In the past I have used something like this: 

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


This fails when multiple users make bookings at the same time.  

I tested the above query by setting the CourseSizeMax to 2, and using WAITFOR time. I scheduled the query to run 3 times; 2 at the same time and the last 1 ms before the other two. They all were able to insert a row, thereby exceeding the session size limit. 

I would really like to avoid using a trigger to handle this; my preference is to use constraints.  I have total control of the design of the database so there is no problem with changing the structure if need be. 

How do I prevent simultaneous updates from breaking the course size limitation?
If you only had two tables, Session and Booking, you could do this:


Add CourseSizeMax column to your dbo.Session table, and add a UNIQUE constraint on [dbo].[Session]([ID], [CourseSizeMax]) - it is needed later.
Add CourseSizeMax column, and BookingNumber  column to your dbo.Booking table.
Add a FK constraint on dbo.Booking(CourseId, CourseSizeMax) referring to Session
Add a CHECK(BookingNumber BETWEEN 1 AND CourseSizeMax)
Add a UNIQUE constraint on dbo.Booking(CourseId, BookingNumber)


and you are all set, as long as all your constraints are trusted.

I am not sure why would you need the third table CourseSize at all.