How to create unique keys across several tables
-
I have several tables that need to have unique keys. No key can be duplicated in any other table. This constraint has been imposed on me by third party software, and they are controlling this through their application, not SQL.
So, if I have a key = 5, this can only be in one table. No other table can have a key of 5.
I have decided that I will create a table to contain my unique keys. Since this is outside of my application data, I will be safe. I think that I will need a "trigger" command to do the actual insert when a new record is created.
I have created my Primary_Key_Table that contains all of the keys as follows:
Primary_Key (Identity)
Table_Name (VarChar)
I also have a table called ‘Table_to_Update’ with the following fields:
Table_ID Int Primary_Key
Last_Name VarChar
First_Name VarChar
Example of my Insert SQL:
Insert Into Table_To_Update ( Last_Name, First_Name ) Values ( 'Smith', 'John' )In my trigger, I can use the following to create my primary key:
CREATE TRIGGER tr_Table_To_Update_Insert ON Table_To_Update FOR INSERT AS Declare @PK AS Int
Insert Into Primary_Key_Table ( Table_Name ) Values ( ‘Table_to_Update’ )
Set @PK = @@Identity
Now, how do I perform the actual insert from here?
What happens if I do a "bulk insert" witrh multiple rows? What will happen if I add the row through Enterprise manager?
Thank you,
Bryan
-
One thought: use a uniqueidentifier column and the NewID. It is somewhat wide, but it is almost certainly going to be unique.
Russel Loski, MCSE Business Intelligence, Data Platform
-
Unfortunately, I can't use the UniqueIdentifier field. I have to keep the ID as an Integer. (Again, this is the 3rd party constraint.
) -
I think that you could use an instead of trigger like what you have. I will let you be the guinea pig.
After your trigger:
insert into Table_to_Update
select @pk, Last_Name, First_Name from inserted
Russel Loski, MCSE Business Intelligence, Data Platform
-
I have been looking around, and that is what I think I have to do as well.
I just didn't know where my data would come from to insert into my table.
What would happen if I tried to do this:
Insert Into Table_To_Update Select Last_Name, First_Name From Table_CustomerAt this point, Will I have to iterate through the "Inserted" table in the trigger (using a Cursor)?
What happens if somebody else is inserting records into other tables that use a trigger? Will SQL keep the data "seperate", or do i have to worry about it?
-
If you have such task then you store same entity in different tables.
Read about normalization rules, think about what do they mean and why people created it, and fix DB design.
Create single table to hold all persons mentioned in your DB and reference it from another tables by its PK.
_____________
Code for TallyGenerator -
How many rows are in each table? You could create the identity in each table and use a different range for each. say 1-1000, 1001-2000, 2001-3000, 3001-4000. If you used a decimal (14,0) as the identy you could have huge ranges in each table. Or you could set each one of the far with a seed of 1,2,3,4 and an increment value of 4. Thus each table would always be unique.
I tested that last one. It worked like a charm.
Tom
-
I really like the second idea, but then again you still could have more tables than the number you taught you'd need... so you'd really be screwed on that one.
Ex : expect 100 tables. Build for 1000. but then again hit 1001 after a few years when they move to SAP?!?!.
-
Yeah, I thought about the 4 limit. As you said you can always build some fluff in. # of tables expected = the increment value. Scope for upto 10 tables make 10 the seed and start 1-10 on the seed values.
Tom
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply