Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
    ) <= 
    (
        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?

share|improve this question

2 Answers 2

If you only had two tables, Session and Booking, you could do this:

  1. Add CourseSizeMax column to your dbo.Session table, and add a UNIQUE constraint on [dbo].[Session]([ID], [CourseSizeMax]) - it is needed later.
  2. Add CourseSizeMax column, and BookingNumber column to your dbo.Booking table.
  3. Add a FK constraint on dbo.Booking(CourseId, CourseSizeMax) referring to Session
  4. Add a CHECK(BookingNumber BETWEEN 1 AND CourseSizeMax)
  5. 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.

share|improve this answer
    
I was writing an almost identical solution/update to my answer last night but urgent issues appeared (see: daughter pressing that fancy blue Reset button). Not sure why CourseSize is needed but if it is, then changing the Session->CourseSize FK to include the CourseSizeMax would solve that issue, too. –  ypercube Mar 29 '12 at 5:42
1  
And that step 1 should be: 1. Add CourseSizeMax column to your dbo.Session table and add UNIQUE ... –  ypercube Mar 29 '12 at 5:43
1  
Also, a UNIQUE constraint on Booking (CourseID, BookingNumber) would be needed, right? –  ypercube Mar 29 '12 at 5:46
    
@ypercube I agree and I have incorporated your fixes in my post. Thanks! –  A-K Mar 29 '12 at 13:16

What you are asking for is a constraint across 2 tables or a table-level constraint with a subquery that involves another table (and that is a limitation of SQL-Server table-level constraints). Check the CREATE TABLE syntax, paragraph Check Constraints:

A column-level CHECK constraint can reference only the constrained column, and a table-level CHECK constraint can reference only columns in the same table.

In SQL-92 standard, there is ASSERTION (a constraint across more than 1 table), which is actually what you would use if it was available. See the asnwers in this question: Why don't DBMS's support ASSERTION for details and for info about some products (MS-Access) that have such functionality with limitations.

Firebird documentation says it allows subqueries in CHECK constraints.

In SQL-Server, your only solution would be a trigger, I'm afraid.

share|improve this answer
    
Thanks for your help ypercube... –  Uncle Mar 29 '12 at 5:24
    
@Uncle: See @Alex's answer. It's a perfectly valid solution that works in your problem, solving it using only declarative constraints. It kind of avoids the problem because only a simple COUNT(*) would be needed in the subquery solution. And having UNIQUE (a) and (a BETWEEN 1 AND size) is the same as (COUNT(*) ...) < size. So, you just have to move (add) the size column to the Booking table and also add that column a (called BookingNumber in the provided solution). –  ypercube Mar 29 '12 at 5:52

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.