CODEWITHSUNDEEP
sqlsql-servertemporaltemporal-tables
Andrew Greatorex
Andrew Greatorex

Reputation: 117

Temporal Table Constraints

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.

Upvotes: 1

Views: 1173

Answers (4)

Kurohoshi
Kurohoshi

Reputation: 113

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.

Upvotes: 1

Gagan Burde
Gagan Burde

Reputation: 328

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.

Upvotes: 0

Gagan Burde
Gagan Burde

Reputation: 328

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.

Upvotes: 0

TheGameiswar
TheGameiswar

Reputation: 28900

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

Upvotes: 2

Related Questions