我必须修复由EntityFramework4自动创建的旧MS SQL
数据库中的一些问题.让我们以下表为例:
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
);
这是一种抽象的实体,实际上我有8个子表来定义类型,其中包括一些其他属性.让我们以此为例:
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
);
也就是说,一个客户每天只能有一个b或c(或两者),理想的约束是UNIQUE
,但由于属性位于不同的表中,这是不可能的(根据我的知识).我采用的解决方案是创建触发器,判断每个子类型的唯一性:
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
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
因为我对MS SQL不是很熟悉,所以我想知道这种方法是否正确,以及是否存在一些性能问题/改进.
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
);
如您所见,该 struct 允许在由e
连接的a
、d
中插入一条记录,其中a.client_id
可能不同于e.client_id
.必须否认这种情况.我定义了一个简单的触发器:
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
有什么更好的解决方案的建议吗?