Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
j2dizzo
Starting Member
6 Posts |
 Posted - 2004-07-10 : 15:00:26
|
| How can I have two auto-increment columns in a table? I used IDENTITY(1,1) but can't have another one on the same table. Any suggestions? |
|
|
gpl
Posting Yak Master
195 Posts |
Posted - 2004-07-10 : 15:36:20
|
| Why would you want this ? they would have the same value ! |
 |
|
|
kselvia
Aged Yak Warrior
526 Posts |
Posted - 2004-07-10 : 17:33:01
|
| You can create a computed column that equals the identity columncreate table #t (id int identity(1,1) , dta char, second_id as id)insert #t (dta) select 'x'select * from #tid dta second_id ----------- ---- ----------- 1 x 1Or did you want the second column to have a different starting value or increment? |
|
|
|
j2dizzo
Starting Member
6 Posts |
Posted - 2004-07-11 : 07:14:54
|
quote:
Originally posted by gpl
Why would you want this ? they would have the same value !
No, they don't need to be the same because autoincrement is the default value. So it'll differ if a value is inserted into the column.quote:
Originally posted by kselvia
Or did you want the second column to have a different starting value or increment?
The intention is to have the auto-increment value as the default value for the two columns. So different values could be inserted or autoincrement values are used if no value is inserted into the columns. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-07-11 : 07:34:38
|
| You can't have a manually overridden value in an IDENTITY column (well, you can INSERT a row with a user-supplier number, but its messy and really only intended for bulk loading existing data).So your best bet is probably to either:1. Have an IDENTITY column and make the value of that column the default in your two columns when there is no user-provided value2. Have a trigger that allocates a number from a table which contains "next available number" if, say, the column has a NULL valueKristen |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-07-11 : 13:59:48
|
| If you inserted a value outside of the autoincrement number, would you want the autoincrement to pick up on the next number higher then that, or just keep going up as normal? What would you do about duplicates if the second way is what you would do?MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
|
|
|
|
|
two auto-increment columns in one table?