CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Dirk
Dirk

Reputation: 225

SQL constraint check with date from table linked with foreign key

I have two tables, created with the following SQL queries:

CREATE TABLE user
(
     user_email varchar(255) not null primary key,
     --other unimportant columns
     subscription_start date not null,
     subscription_end date,
     CONSTRAINT chk_end_start CHECK (subscription_start != subscription_end)
)

CREATE TABLE action
(
     --unimportant columns
     user_email varchar(255) not null,
     action_date date not null,
     CONSTRAINT FK_user FOREIGN KEY (user_email) REFERENCES user(user_email)
)

What I would like to do is make sure with some sort of check constraint that the action_date is between the subscription_start and subscription_end.

Upvotes: 0

Views: 266

Answers (4)

Antonio
Antonio

Reputation: 535

actually, an indexed view requires "reindexing" when data has changed in the table. Performance will suffer if it's a rather complex view or has lotos of rows. I also believe that a trigger in this case would perform better...

Upvotes: 0

Dan
Dan

Reputation: 10680

This is not possible to do using check constraints, since check constraints can only refer to columns inside the same table. Furthermore, foreign key constraints only support equi-joins.

If you must perform this check at the database level instead of your application level, you could do it using a trigger on INSERT/UPDATE on the action-table. Each time a record is inserted or updated, you check whether the action_date lies within the corresponding subscription_start/end dates on the user-table. If that is not the case, you use the RAISERROR function, to flag that the row can not be inserted/updated.

CREATE TRIGGER ActionDateTrigger ON tblaction
AFTER INSERT, UPDATE
AS
IF NOT EXISTS (
    SELECT * FROM tbluser u JOIN inserted i ON i.user_email = u.user_email
      AND i.action_date BETWEEN u.subscription_start AND u.subscription_end
)
BEGIN
    RAISERROR ('Action_date outside valid range', 16, 1);
    ROLLBACK TRANSACTION;
END

Upvotes: 2

GarethD
GarethD

Reputation: 69789

I always try to avoid triggers where possible, so thought I would throw an alternative into the mix. You can use an indexed view to validate data here. First you will need to create a new table, that simply contains two rows:

CREATE TABLE dbo.Two (Number INT NOT NULL);
INSERT dbo.Two VALUES (1), (2);

Now you can create your indexed view, I have used ActionID as the implied primary key of your Action table, but you may need to change this:

CREATE VIEW dbo.ActionCheck
WITH SCHEMABINDING
AS
    SELECT  a.ActionID
    FROM    dbo.[User] AS u
            INNER JOIN dbo.[Action] AS a
                ON a.user_email = u.user_email
            CROSS JOIN dbo.Two AS t
    WHERE   a.Action_date < u.subscription_start
    OR      a.Action_date > u.subscription_end
    OR      t.Number = 1;
GO;
CREATE UNIQUE CLUSTERED INDEX UQ_ActionCheck_ActionID ON dbo.ActionCheck (ActionID);

So, your view will always return one row per action (t.Number = 1 clause), however, the row in dbo.Two where number = 2 will be returned if the action date falls outside of the subscription dates, this will cause duplication of ActionID which will violate the unique constraint on the index, so will stop the insert. e.g.:

INSERT [user] (user_email, subscription_start, subscription_end) 
VALUES ('[email protected]', '20140101', '20150101');

INSERT [Action] (user_email, action_date) VALUES ('[email protected]', '20140102');
-- WORKS FINE UP TO THIS POINT

-- THIS NEXT INSERT THROWS AN ERROR
INSERT [Action] (user_email, action_date) VALUES ('[email protected]', '20120102');

Msg 2601, Level 14, State 1, Line 1

Cannot insert duplicate key row in object 'dbo.ActionCheck' with unique index 'UQ_ActionCheck_ActionID'. The duplicate key value is (6).

The statement has been terminated.

Upvotes: 3

Eduard Uta
Eduard Uta

Reputation: 2617

The solution is like so:
- in table action create a check constraint that calls a function like so: check(dbo.FcToCheckValidity(user_email, action_date) = 1)
- the dbo.FcToCheckValidity function would be something like:
-- FcToCheckValidity(user_email , action_date) returning BIT
-- query user table and: if input action_date between subscription_start and subscription_end for the correct user THEN return 1 else 0.

Upvotes: 0

Related Questions