有没有一种方法可以创建约束,或者在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

希望有人能理解我要归档的内容。

提前致谢。

最佳答案

考虑使用:

  • 1或许多持久的计算列
  • 对持久化计算列的唯一约束

  • 各种持久化列的一些示例(非常感谢Aaron Bertrand):
  • Y年(SOMEDATE)持续存在
  • 保留的M个月(SOMEDATE)
  • D月(持续)保留
  • YMD年(SOMEDATE)* 10000 +月(SOMEDATE)* 100 + DAY(SOMEDATE)保留
  • SOMEDATEDIFF AS DATEDIFF(DAY,0,SOMEDATE)保留
  • SOMEDATESMALL AS DATEADD(DAY,0,DATEDIFF(DAY,0,SOMEDATE))保留
  • SOMEDATESMALL AS CONVERT(DATETIME,FLOOR(CONVERT(FLOAT,SOMEDATE,105)),105)保留
  • 持久保留
  • SOMEDATESMALL作为转换(日期,SOMEDATE)-需要SQL Server 2008

  • 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/

    10-14 17:00