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 |
|
msc4812
Starting Member
2 Posts |
 Posted - 2004-11-06 : 01:59:05
|
| Hi,I try to figure out what the best solution would be to share the identity of one table with another table but still be unique for both tables.Well I try to explain what I try to achieve. There is one "old" table, which I shouldn't touch. But I have to design a new table where I which should have a column being unique to the identity of the "old" table. What was coming up in my mind is to explicit get the next value of the identity sequence of the old table.Let’s say if I insert a row in the new table value 20, then the next row in the old table in the identity field should be 21.I hope my description does make sense.Any suggestion would be highly appreciated.Cheers |
|
|
Kristen
Test
22859 Posts |
Posted - 2004-11-06 : 02:23:50
|
| Are you trying to work out how to allocate a number, or to put a constraint to prevent accidental allocation of duplicates?For the constraint I expect you could create a VIEW with a UNION of the two IDs and put a unique index on that. Pretty messy though IME.Can you set an IDENTITY number on the two tables where on is EVEN and the other ODD?Kristen |
 |
|
|
msc4812
Starting Member
2 Posts |
Posted - 2004-11-06 : 02:33:45
|
| Thanks Kristen,actually, i like the even and odd idea. very creative problem solving skills.one thing still in my mind, (might be oracle specific) that you can say something like "sequnce next value", and just use the same sequence for both tables.for the moment i use the even and odd idea. I have to show that value in a report, and there for the numbers will grow in the doubble the speed tough.thanks and still open for other suggestions!cheers |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2004-11-06 : 05:53:20
|
| Put a check constraint on the tables to make sure that the generated identities really are odd/even.check( id%2 = 1 ) -- oddcheck( id%2 = 0 ) -- evenrockmoose |
|
|
|
|
|
|
Share Identity