Temporal Table Constraints

Question

I've been looking into temporal tables recently and I've applied them successfully to one of my tables. I need to apply it to other tables in the database so I ran a script to which was like the example below. However when running it on my 2nd table I get a

Msg 2714, Level 16, State 5, Line 1 There is already an object named 'DF_ValidFrom' in the database.

The article below seems to imply you can use the same constraint across the Product and Location and tables. Anyone got any experience in altering existing tables to temporal tables?

ALTER TABLE Product   
ADD   
    SysStartTime datetime2 (2) GENERATED ALWAYS AS ROW START HIDDEN    
        constraint DF_ValidFrom DEFAULT DATEADD(second, -1, SYSUTCDATETIME())  
    , SysEndTime datetime2 (2)  GENERATED ALWAYS AS ROW END HIDDEN     
        constraint DF_ValidTo DEFAULT '9999.12.31 23:59:59.99'  
    , PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime);   

ALTER TABLE Product    
    SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.ProductHistory));  

ALTER TABLE [Location]  
ADD   
    SysStartTime datetime2 (2) GENERATED ALWAYS AS ROW START HIDDEN    
        constraint DF_ValidFrom DEFAULT DATEADD(second, -1, SYSUTCDATETIME())  
    , SysEndTime datetime2 (2)  GENERATED ALWAYS AS ROW END HIDDEN     
        constraint DF_ValidTo DEFAULT '9999.12.31 23:59:59.99'  
    , PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime);  

ALTER TABLE [Location]    
    SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.LocationHistory));

Full article is here https://learn.microsoft.com/en-us/sql/relational-databases/tables/temporal-table-usage-scenarios

Thanks in advance for any help.

Constraint like other objects, need different name for save in sql server data default tables. you just change name **DF_validfrom** to another name. — Amirhossein, Apr 06 '20 at 10:18

TheGameiswar · Answer 1 · 2017-08-01T09:24:13.723

2

No ..you can't have two constraints in same database..The article seems to imply location and employee table as seperate examples

you can also look out this answer for more on constraints : https://stackoverflow.com/a/1397674/2975396

i have submitted a pull request making necessary changes,so this won't be an issue any more

edited Aug 01 '17 at 09:24

answered Aug 01 '17 at 09:10

TheGameiswar

27,855
8
56
94

What happened with your pull request? The docs still have this issue – Martin Smith Dec 31 '18 at 13:09
@MartinSmith,Actually i remeber it was fixed at that time.Will check again – TheGameiswar Dec 31 '18 at 13:34
The link here https://learn.microsoft.com/en-us/sql/relational-databases/tables/temporal-table-usage-scenarios?view=sql-server-2017 and searching for `ALTER TABLE Product ` still seems to have the problem – Martin Smith Dec 31 '18 at 13:36
@MartinSmith :Here is a pull request they accepted(https://github.com/MicrosoftDocs/sql-docs/commit/0cc1e99439f6eb69e8f55aba1eace8c2c7221d5d) and i could see it is correct..Earlier it is `DF_ValidFrom` in both cases ,now it is `DF_ValidFrom` and `DFValidFrom` – TheGameiswar Dec 31 '18 at 13:42
1

Ah yes - didn't spot the underscore difference – Martin Smith Dec 31 '18 at 13:43

score 1 · Answer 2 · answered Jul 30 '18 at 17:02

Your issue is due to the fact that those constraints already exist in your schema. Constraint names must be unique. You can get around this issue by choosing a different name for the constraints. Using your example I would create my tables like this:

ALTER TABLE Product   
ADD   
    SysStartTime datetime2 (2) GENERATED ALWAYS AS ROW START HIDDEN    
        constraint DF_Prodcut_ValidFrom DEFAULT DATEADD(second, -1, SYSUTCDATETIME())  
    , SysEndTime datetime2 (2)  GENERATED ALWAYS AS ROW END HIDDEN     
        constraint DF_Prodcut_ValidTo DEFAULT '9999.12.31 23:59:59.99'  
    , PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime);   

ALTER TABLE Product    
    SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.ProductHistory));  

ALTER TABLE [Location]  
ADD   
    SysStartTime datetime2 (2) GENERATED ALWAYS AS ROW START HIDDEN    
        constraint DF_Location_ValidFrom DEFAULT DATEADD(second, -1, SYSUTCDATETIME())  
    , SysEndTime datetime2 (2)  GENERATED ALWAYS AS ROW END HIDDEN     
        constraint DF_Location_ValidTo DEFAULT '9999.12.31 23:59:59.99'  
    , PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime);  

ALTER TABLE [Location]    
    SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.LocationHistory));

As you can see the I just added the table name between the DF and ValidFrom/ValidTo in the constraint name. This should allow you to create all of the temporal tables you desire.

It's **work well** and Clear answer. just we cant use same object name in one section. likely tabel name, column name and etc... its SQL server **law**. — Amirhossein, Apr 06 '20 at 10:15

score 0 · Answer 3 · answered Nov 10 '17 at 12:19

I got also same problem and found out that it is due to constraint only. In temporal table you are adding two columns valid from and valid to .So while altering these table we are adding DF_ValidFrom and DF_ValidTo constraint. So if you are adding just check that already you have executed your script or not otherwise you need to delete such constraint and execute alter table script.

score 0 · Answer 4 · answered Nov 13 '17 at 14:08

0

For perfectly work just add constraint with different name. This will help you to run execute your script properly. We did in our project for almost 50 tables.

answered Nov 13 '17 at 14:08

Gagan Burde

328
2
13

Temporal Table Constraints

4 Answers4