I'm using MS SQL SERVER 2019.

In my database I have a table Events which stores events offered by a club

Events (Theme, Event_Date, Place, Event_Hour, Type)

Then I also have a table Reservations which stores reservations for those events:

Reservations (Id, Event_Theme)       FK(Event_Theme) --> PK(Events.Theme)

My goal is to allow insertion of new rows in Reservations only if Event_Theme for that row is a theme of a future event (i.e one with Event_Date > CURRENT DATE): obviously reservations are not allowed for past events.

My attempt was the creation, inside Reservations table's creation, of the following check constraint:

  CONSTRAINT CHK_Reservations_Event_Theme   CHECK (Event_Theme IN (SELECT STRING_AGG(E.Theme, ',') WITHIN GROUP (ORDER BY E.Theme ASC)
                                                                   FROM Events E
                                                                   WHERE E.Event_Date>=(CAST( GETDATE() AS Date))))

But I then remembered that subqueries aren't supported for check constraints

What is an alternative way to implement the same logic?

CodePudding user response：

As you can see in the example event number 2 is outdated, so a new row is not inserted.

Event number 1 can be inserted.

you can add more and more conditions to the WHERE for example if only 100 reservations can be made

CREATE TABLE Events (Theme int , Event_Date date, Place varchar(4), Event_Hour int, Type int)

INSERT INTO Events VALUES(1, DATEADD(month, 1, getdate()), 'A',1,1),
(2, DATEADD(day, -1, getdate()), 'B',2,2)

CREATE tABLE Reservations (Id int, Event_Theme int) 

 CREATE TRIGGER tr_Reservations ON Reservations
INSTEAD OF INSERT 
AS
BEGIN 

INSERT INTO [Reservations](
  [Id],
  [Event_Theme])
SELECT  Id,[Event_Theme]  
FROM INSERTED
WHERE Event_Theme IN (SELECT STRING_AGG(E.Theme, ',') WITHIN GROUP (ORDER BY E.Theme ASC)
                                                                   FROM [Events] E
                                                                   WHERE E.Event_Date>=(CAST( GETDATE() AS Date)));
PRINT 'success.';

  END

INSERT INTO Reservations VALUES (1,1),(2,2)

success. 3 rows affected

SELECT * FROM Reservations

Id | Event_Theme
-: | ----------:
 1 |           1

db<>fiddle here

CodePudding user response：

I found also a way without using a trigger.

I created the following stored function:

CREATE FUNCTION usf_CHECK_THEME(@Event_Theme VARCHAR(45))
RETURNS VARCHAR(45)
BEGIN
    DECLARE @Theme VARCHAR(45);
    SET @Theme =NULL;
    SET @Theme = (SELECT E.Theme
                 FROM  Events E
                 WHERE E.Theme=@Event_Theme  AND E.Event_Date>(CAST(GETDATE() AS Date)));
RETURN @Theme;
END;

Theme is PK In table Events.

And then in the check constraint:

CONSTRAINT CHK_Reservations_Event_Theme CHECK (usf_SCEGLINOME(Eventi_Pubblici_Tema) IS NOT NULL)