How to create unique keys across several tables

Question

How to create unique keys across several tables

Bryan P. Clauss

SSC Eights!

Points: 959
More actions
January 16, 2007 at 10:56 am

#153387
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

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Russel Loski SSCertifiable Points: 7059 More actions · Answer 1

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

Bryan P. Clauss SSC Eights! Points: 959 More actions · Answer 2

Unfortunately, I can't use the UniqueIdentifier field. I have to keep the ID as an Integer. (Again, this is the 3rd party constraint. )

Russel Loski SSCertifiable Points: 7059 More actions · Answer 3

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

Bryan P. Clauss SSC Eights! Points: 959 More actions · Answer 4

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_Customer

At 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?

Sergiy SSC Guru Points: 110208 More actions · Answer 5

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

Tom Goltl SSCertifiable Points: 5815 More actions · Answer 6

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

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?!?!.

Tom Goltl SSCertifiable Points: 5815 More actions · Answer 8

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