有没有一种方法可以创建约束,或者在FK列中某天我只能有一条记录?即
PK(IDENTITY(1,1)) FK_FIELD DATETIME
------------------------------
1 1 2012-02-22 4:50 p.m.
2 2 2012-02-23 12:00 p.m.
3 2 2012-02-23 9:00 p.m. -- This one should not be allowed because the FK_Field 2 would have 2 record in one day.
4 1 2012-02-23 08:00 a.m
希望有人能理解我要归档的内容。
提前致谢。
最佳答案
考虑使用:
各种持久化列的一些示例(非常感谢Aaron Bertrand):
N.B.仔细选择这些示例以确保功能确定性。
这是一个示例SQL:
CREATE TABLE DATA
(
ID INT NOT NULL IDENTITY,
FK_FIELD INT NOT NULL,
SOMEDATE DATETIME NOT NULL,
SOMEDATESMALL AS DATEADD(DAY, 0, DATEDIFF(DAY, 0, SOMEDATE)) PERSISTED,
CONSTRAINT PK_DATA PRIMARY KEY (ID),
CONSTRAINT UQ_DATA_SOMEDATESMALL UNIQUE (FK_FIELD, SOMEDATESMALL)
);
如果您尝试执行INSERT,那么您将获得:
INSERT INTO DATA (FK_FIELD, SOMEDATE) VALUES (1, '2012-02-22 4:50PM'); -- SUCCESS
INSERT INTO DATA (FK_FIELD, SOMEDATE) VALUES (2, '2012-02-23 12:00PM'); -- SUCCESS
INSERT INTO DATA (FK_FIELD, SOMEDATE) VALUES (2, '2012-02-23 9:00PM'); -- CAUGHT
INSERT INTO DATA (FK_FIELD, SOMEDATE) VALUES (1, '2012-02-23 8:00AM'); -- SUCCESS
SQL Server Management Studio中显示的消息是:
关于sql - SQL约束:特定键每天仅一条记录,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/9421745/