question

ruancra avatar image
ruancra asked

Table Constraint - Unique value

Hi all I need to add a constraint to a column, so that the first time a row gets inserted, the column gets a value of 1000000 and the next insert increments by 1 all the time. Thx
tsqlconstraint
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image
Grant Fritchey answered
If you were on SQL Server 2012 you can use a [SEQUENCE][1]. If not, as much as I loathe them, a trigger might be the way to go. [1]: http://msdn.microsoft.com/en-us/library/ff878091.aspx
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ruancra avatar image ruancra commented ·
Yeah,also thought about a trigger, thx Grant..
0 Likes 0 ·
ThomasRushton avatar image
ThomasRushton answered
You could try using an [`IDENTITY`][1] field: CREATE TABLE MyTable (BigIndex INT IDENTITY(1000000, 1), Field1 as int, Field2 as varchar(50)) INSERT INTO MyTable (Field1, Field2) VALUES (Value1, Value2) -- etc Then, when you insert into that table, only insert into the other fields - the IDENTITY will take care of itself. For extra enforcement, set a unique constraint on that field. [1]: http://msdn.microsoft.com/en-us/library/ms186775.aspx
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ruancra avatar image ruancra commented ·
Thanks Thomas, problem is, i already have a identity column, and 2 identity columns cannot exist on a table.
0 Likes 0 ·
ThomasRushton avatar image ThomasRushton ♦♦ commented ·
Oh, OK. Why not use a computed field based off that IDENTITY field?
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.