Table Constraint - Unique value

Question

question

ruancra asked Jun 13, '13

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

tsql constraint

10 |1200

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

Answer 1 · 2013-06-13T09:31:33Z

Grant Fritchey answered Jun 13, '13

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

10 |1200

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

ruancra commented · Jun 13, 2013 at 10:17 AM

Yeah,also thought about a trigger, thx Grant..

0 ·

Answer 2 · 2013-06-13T08:36:38Z

ThomasRushton answered Jun 13, '13

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

10 |1200

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

ruancra commented · Jun 13, 2013 at 08:54 AM

Thanks Thomas, problem is, i already have a identity column, and 2 identity columns cannot exist on a table.

0 ·

ThomasRushton ♦♦ commented · Jun 13, 2013 at 09:12 AM

Oh, OK. Why not use a computed field based off that IDENTITY field?

0 ·

question