tegronakron
asked on
Primary Keys and Merge Replication
We are working on a SQL2005 application that uses Merge Replication to publish data to 12-16 client subscribers.
I don't like the idea of using indentity columns for primary keys. I am also concerned about buiding a composite key that uses a machine identifier and date/time stamp because of the potential for duplicating keys when the clocks are set back for daylight savings time. GUIDs are too large to be included in the PK.
What other options are there?
I don't like the idea of using indentity columns for primary keys. I am also concerned about buiding a composite key that uses a machine identifier and date/time stamp because of the potential for duplicating keys when the clocks are set back for daylight savings time. GUIDs are too large to be included in the PK.
What other options are there?
There are two orher alternatives.
1. Natural keys. Typically some combination of name, revision, etc is going to have a unique constraint placed on it. The advantage of using natural keys is that you do not need to manage anything to introduce artificial or "hidden" keys. The disadvantages arise when you want to put them in other tables in foreign key references. A) You may have to add a lot of columns to get one composite key, and B) How do you deal with the issue of renaming a thing. You can UPDATE CASCADE in SQL Server, but others do not.
2. Create your own artifical key manager. For example, add a table called key_supply as:
CREATE TABLE key_supply
(
table_name VARCHAR(32),
next_key INTEGER
);
Then create a procedure that does a SELECT FOR UPDATE given a table name, and returns the new key. Works a lot like an identity, but you manage it yourself. I agree with you about guids.
Jim
1. Natural keys. Typically some combination of name, revision, etc is going to have a unique constraint placed on it. The advantage of using natural keys is that you do not need to manage anything to introduce artificial or "hidden" keys. The disadvantages arise when you want to put them in other tables in foreign key references. A) You may have to add a lot of columns to get one composite key, and B) How do you deal with the issue of renaming a thing. You can UPDATE CASCADE in SQL Server, but others do not.
2. Create your own artifical key manager. For example, add a table called key_supply as:
CREATE TABLE key_supply
(
table_name VARCHAR(32),
next_key INTEGER
);
Then create a procedure that does a SELECT FOR UPDATE given a table name, and returns the new key. Works a lot like an identity, but you manage it yourself. I agree with you about guids.
Jim
BTW - I would be very wary of the timestamp. Anyone who can get to the server can reset the clock. And, you never know how close together inserts may come in a large OLTP system with lots of users.
Jim
Jim
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
For the daylight savings problem you can add the timezone to your primary key to avoid duplicate records.