unique column in 2 tables - interesting problem
-
I have a strange one here. A developer came to me and apparently they have 2 tables with an int column in both tables that they want to be sequential between the 2 tables. so if you insert a record in the first table as ID 12, then the next record in the second table would need to be 13. I have thought of a few ideas that would probably work, but didn't sound like good, solid solutions. Any ideas?
-
Adam
It sounds like poor database design to me - are we allowed to know why they want to do this?
Anyway, to answer your question. How about creating a table with an identity column and a bit column. Then, when you insert a row into one of the two tables, you can take the smallest value in the identity column that doesn't have the "Used" bit on, and switch it on so that the next insert uses the next one up. Of course there will be chaos when someone does an insert that bypasses this procedure, or updates the "identity column" in either of your tables.
There's probably a way of doing this with triggers, as well, but I'll leave someone else to suggest that.
John
-
Unfortunately, I don't have an answer as to why this is the way it is. It's already in production and they got a dupe in one of the tables, hence they are looking for a better way to do it.
Thanks for the idea, a seperate table with an identity is one solution I thought of. I also thought of using triggers, and that would probably be better for concurrency issues, which is what they ran into to create the dupe record.
Sorry I don't have more info for you.
Any other Ideas? So far I'm leaning towards triggers.
-
My 2 cents... IDENTITY column on table "A" with trigger on table "A" that adds 1 to that column to store in table "B". Then, kill the person that designed it
That will give you something like this...
TableA TableB
12 13
13 14
14 15
They're not talking about something like this, are they???
TableA TableB
12 13
14 15
16 17
If, so, use the same method except the IDENTITY column would need an increment of 2 instead of 1.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Good suggestion!
yes, the second example is exactly what we are looking for, although it doesn't necessarily need to be every other row. as long as the ID is not duplicated in either table, that is what we are looking for:
This would be acceptable as well, the main issue is no reuse of anuy of the numbers in either table. Obviously the designer is no longer with us....
TableA tableB
1 2
3 5
4 6
-
- as long as you keep in mind that identity may leave you some gaps
- when you use objects with identity in parallel (insert with multiple users) you'll see it leaves gaps by design !! It preserves ranges to server the diverse threads !
Aparently they want kind of an "order" column.
Why not use a datetime column (+- 0.003 seconds) ?
If this suites you, define it with default getdate or getutcdate
If you have two objects pulling from the same parametertable, it's normal they suffer contention.
Why don't they put it in a single table ?
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
How about 1 parent table A with identity, 2 child tables with foreign key and check constraints: table B, all the ids must be divisible by 2 with a remainder of 1; table C, all the ids must be exactly divisible by 2.
Then, as others have suggested, go find the one who designed this mess and slap 'em upside the head.
-
Thanks all for your suggestions on how to handle this mess. The designer has ran off to a far away location for obvious reasons. Thanks again!
-
How about a separate table that holds the identity sequence and a "getnextid" stored procedure on each of the other tables?
-
Consider Identity columns with odd and even seed values, both incremented by 2:
CREATE TABLE A (id int IDENTITY(1,2), ...) -- 1,3,5...
CREATE TABLE B (id int IDENTITY(2,2), ...) -- 2,4,6...
-
Since you are looking for 1-up numbers and the numbers have to be unique between two tables and may not be in alternating order, I suggest sticking to a TRIGGER.
You need to create an insert trigger on both tables that checks the MAX(idcolumn) number from the other table and adds 1 to it before inserting the data.
-SQLBill
-
You could try a simpler option.
Create a third table that has only an identity column. Change each of the two tables to have a foreign key relationship with the third table. To insert a new row with a unique key, simply insert a row into the new parent table, and use the new key value in whichever table is being addressed. In fact, you could probably code this into an insert trigger on each of the existing tables so the developer would be none the wiser. (But then, wasn't that the problem in the first place????)
Steve G.
-
mkeast's suggestion is probably the best so far for avoiding conflicts. However, since we're all having fun suggesting varied solutions, here's my two cents:
Roll the two source tables together into one table (say "CoreTable"), with a single Indetity column as the primary key, and a second column (say "SourceTable") indicating which table the row came from. Create schema-bound views from CoreTable to replace your source tables, with Insert triggers to set the SourceTable column to the correct value. That way, you could eventually transition the code to use just the core table, but in the interim everything still works. Plus, you get primary keys that look like this:
CoreTable SourceTableA SourceTableB
1, A 1
2, A 2
3, B 3
4, B 4
5, B 5
6, A 6
Example code:
begin
tran
create table dbo.CoreTable (a int identity(1,1) primary key, b char(1), c int)
GO
-- SchemaBinding is just so no one can screw up the CoreTable without alter the view
create view SourceTableA with schemabinding as
select a, c
from dbo.coretable
where b = 'A'
GO
create
trigger trgSourceTableA_insert on SourceA
instead of insert as
insert into CoreTable (b, c)
select 'A', c
from inserted
GO
create view SourceTableB with schemabinding as
select a, c
from dbo.coretable
where b = 'B'
GO
create
trigger trgSourceTableB_insert on SourceB
instead of insert as
insert into CoreTable (b, c)
select 'B', c
from inserted
GO
insert into SourceTableA (c) values (1);
insert into SourceTableA (c) values (2);
insert into SourceTableB (c) values (3);
insert into SourceTableA (c) values (4);
insert into SourceTableB (c) values (5);
select
* from coretable
select * from SourceTableA
select * from SourceTableB
rollback
Rick
townsends.ca -
That's a great suggestion... guaranteed to not duplicate between tables and no trigger overhead.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
I just want to know how the app knows which of the two tables to insert.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply