I have to fix some problems in an old MS SQL database, autocreated by entityframework 4. Let's take as example the following tables:
CREATE TABLE a (
id int NOT NULL PRIMARY KEY,
date datetime NOT NULL,
client_id int NOT NULL //references to clients table
-- other properties related to entity a
);
This is a sort of an abstract entity, actually I have 8 sub-tables which define the type which include some other properties. Let's take as example:
CREATE TABLE b (
id int NOT NULL PRIMARY FOREIGN KEY REFERENCES a (id)
-- other properties related to entity b
);
CREATE TABLE c (
id int NOT NULL PRIMARY FOREIGN KEY REFERENCES a (id)
-- other properties related to entity c
);
That said, a client can have only one b or c (or both) per day, the ideal constraint would be an UNIQUE constraint but due to the properties being in different tables it is not possible (based on my knowledge). The solution I adopted is to create triggers, which check the uniqueness of each subtype:
CREATE TRIGGER trigger_b_upsert
ON b
AFTER INSERT, UPDATE
AS
IF EXISTS (
SELECT 1
FROM a a
JOIN b b ON a.id = b.id
WHERE a.client_id IN (
SELECT client_id
FROM a a
JOIN inserted i ON a.id = i.id
) AND date IN (
SELECT date
FROM a a
JOIN inserted i ON a.id = i.id
)
GROUP BY date, client_id
HAVING count(*) > 1
)
BEGIN
RAISERROR ('not allowed.', 10, 1)
ROLLBACK TRANSACTION
END
and the same for c.
INSERT INTO a (id, date, client_id) VALUES (1, '2050-01-01', 1);
INSERT INTO b (id) VALUES (1);
INSERT INTO a (id, date, client_id) VALUES (2, '2050-01-01', 1);
INSERT INTO b (id) VALUES (2); -- should raise error
INSERT INTO a (id, date, client_id) VALUES (1, '2050-01-01', 1);
INSERT INTO b (id) VALUES (1);
INSERT INTO a (id, date, client_id) VALUES (2, '2050-01-01', 1);
INSERT INTO c (id) VALUES (2); -- should work
INSERT INTO a (id, date, client_id) VALUES (1, '2050-01-01', 1);
INSERT INTO a (id, date, client_id) VALUES (2, '2050-01-02', 1);
INSERT INTO a (id, date, client_id) VALUES (3, '2050-01-01', 1);
INSERT INTO a (id, date, client_id) VALUES (4, '2050-01-02', 1);
INSERT INTO b (id) VALUES (1);
INSERT INTO b (id) VALUES (2); -- should work
INSERT INTO c (id) VALUES (3);
INSERT INTO c (id) VALUES (4); -- should work
Because I am no really familiar with MS SQL, I would like to know if the approach is correct or not, and if there are some performance issues/improvments.
Edit:
@GarethD has shown a brillant solution to my problem, but I have another case of trigger I need to improve, if possible. Let's take the same table a and define two new ones:
-- another table, different from a, which as well references the client
CREATE TABLE e (
id int NOT NULL PRIMARY KEY,
client_id int NOT NULL //references to clients table
-- other properties related to entity e
);
CREATE TABLE d (
id int NOT NULL PRIMARY FOREIGN KEY REFERENCES a (id)
e_id int NOT NULL PRIMARY FOREIGN KEY REFERENCES e (id)
-- other properties related to entity d
);
As you can see, the structure allows to insert a record in a, d connected by a e, where a.client_id could be different from e.client_id. This scenario has to be denied. I defined a simple trigger:
CREATE TRIGGER trigger_d_upsert
ON d
AFTER INSERT, UPDATE
AS
IF EXISTS (
SELECT 1
FROM a a
JOIN inserted i ON a.id = i.id
JOIN e e on e.id = i.e_id
WHERE e.client_id != a.client_id
)
BEGIN
RAISERROR ('Conflict client_id.', 10, 1)
ROLLBACK TRANSACTION
END
Any hints for a nicer solution?
I've never been a fan of using triggers where I can avoid them, so I would personally use indexed views here to manage the uniqueness, one for each child table:
CREATE VIEW dbo.UniqueB
WITH SCHEMABINDING
AS
SELECT a.client_id, a.date
FROM dbo.a
INNER JOIN dbo.b ON b.id = a.id;
GO
CREATE UNIQUE CLUSTERED INDEX UQ_UniqueB__clientid_date ON dbo.UniqueB (client_id, date);
This will constrain your records to be unique in the same way as the trigger but in my opinion is a more elegant solution, and although not tested I expect it would be more efficient too. An attempt to insert a duplicate would generate a message such as:
Msg 2601 Level 14 State 1 Line 1
Cannot insert duplicate key row in object 'dbo.UniqueB' with unique index 'UQ_UniqueB__clientid_date'. The duplicate key value is (1, Jan 1 2050 12:00AM).
Firstly, please don't edit your questions to ask a second question, just ask a whole new question and if necessary link back to the first one to provide additional context.
But to actually answer your question while I struggle to think of a real usecase for this, the way I think I would deal with this scenario is to actually include the client_id column in the table d. This does introduce redundancy, but it's a trade off I personally would be willing to make. To make this work fully you also need additional unique constraints on table a and table e:
CREATE TABLE dbo.a (
id int NOT NULL PRIMARY KEY,
date datetime NOT NULL,
client_id int NOT NULL ,
UNIQUE(id, client_id)
);
CREATE TABLE e (
id int NOT NULL PRIMARY KEY,
client_id int NOT NULL,
UNIQUE(id, client_id)
);
With these in place you can now reference these in table d:
CREATE TABLE d (
id int NOT NULL PRIMARY KEY FOREIGN KEY REFERENCES dbo.a (id),
e_id int NOT NULL FOREIGN KEY REFERENCES dbo.e (id),
client_id INT NOT NULL,
FOREIGN KEY (id,client_id) REFERENCES dbo.a (id, client_id),
FOREIGN KEY (e_id,client_id) REFERENCES dbo.e (id, client_id)
);
This means that an attempt to add mis-matching client_ids to table d will result in a foreign key error to either table a or table d.
The benefit of this approach over a trigger is that it captures all changes. If you only put your trigger on table d, you won't capture other changes, for example even with the trigger in place the following queries runs OK:
UPDATE a
SET client_id =2
WHERE id =1;
Which then leaves mis-matching records. The same would be true of an update to table e.