November 3, 2008 at 6:34 pm
Hi,
I have this application that i am writing, I have about 20 or so tables. 4 of these tables deal with Manual Tickets used for logistics....Now my question is....when i use the @@IDENTITY to get the last autoincremented number(which i use for the primary key as well) from the insert statement, it ( the auto key / PK ) gets passed to other tables as well, now when i go to use an inner join and just say i query the result using the PK, will i not get information that involves other tickets that possibly have the same auto key / PK that I query?
If so how do i go about making sure that this doesn't happen, i am thinking of using a guid but from what i read it a pretty big number(16 bits) and that is alot of space, and that the query would run slower.
I could use an alternate key but would that cause the query to run slower?
here is an example where i think i will get wrong data
when i created a new ticket, i take that pk and insert it to a weights table that contains gross, tare and net and the ticket's pk.
If i inner join on that and there is 3 other different ticket types which have the same auto incremented id/ pk will i not get wrong data?
Which is the best way to do this so i don't get wrong data?
Thanks in advance
Chris
November 4, 2008 at 8:26 am
Hi Chris,
You have asked a number of questions and I am not sure there is sufficient information to answer all of them.
If you properly define the referential integrity between your primary key table and the table you are join then you will not get incorrect results. So assume ParentTab is the table that has an auto-increment field PriKey and dataTab is a related table. DataTab will have a field, say ParentId, that is defined as a foreign key to ParentTab. You set up the relationship with the following bit of T-SQL
Alter table DataTab Add Constraint FK_DataTab_ParentTab
Foreign Key (ParentId) References ParentTab.PriKey
I use autoincrement fields a lot when I am given the choice. You can use them effectively as a surrogate primary key and then create a unique constraint on the natural key.
BTW UniqueIdentifiers are 16 Byte not 16 bit values. They are a good choice when you need to maintain unique identity across systems. However, they should not be used for clustered indexes and IMHO really should not be the primary key.
November 4, 2008 at 1:51 pm
Thanks for the reply.
I see what you are saying. I found a solution to my problem today while i was at work...
What i was doing and not realizing was I was using one to many between my Ticket table and weight table. What i figured to do was do a one to many from the weight table to the ticket table.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply