custom identity field

I have what I thought would be a simple task but I keep hitting dead ends.
My database has many tables, each with an identity column as the primary
key. This worked fine until we had a requirement to import rows from other
sites/database installs (same tables, different servers). We won't have
very many databases, but the table keys must be unique to the database and
the sites. Since our users refer to the rows by ID, GUIDs are far too
awkward and will not work. The key needs to work much like the identity
field, easy to query the last key added and the key is generated
automagically.
The easy solution would be to just add the site code (3 letter alpha) to the
keys. We had hoped to just create a simple function that would return the
key and we could set the default value of the key to point to the function.
For example, the table User would have a primary key of UserID with a
default value of getKey('user') which would return 'AAA1' for the first user
entered in site 'AAA'. If the first user from site 'BBB' was imported, it
would be simple to synchronize and determine at a glance as we would have a
user 'BBB1'.
After playing around with functions, procs, default values, creating system
functions, formulas, triggers, we have not found a way to do what we want.
Anybody have any advice on this? Is there a better way?
Don't use IDENTITY keys to maintain integrity between databases - it's a
waste of time. Use alternate keys for that. The only sensible use of an
IDENTITY key is as a SURROGATE so just assign new IDENTITY keys (parent and
foreign keys) when you import the data.
David Portas
SQL Server MVP
|||>> "Don't use IDENTITY keys...Use alternate keys..."
That is exactly my question. I stated that I cannot use IDENTITY keys and
want to create my own alternate key. How can I generate an alternate key
that can, as seemlessly as possible, replace the IDENTITY key? I've tried
to create system functions, triggers, and procs but cannot seem to find a
mechanism whereby I can autogenerate a default value for the row based on a
procedure call.
|||I don't see a problem. Your tables on the source systems should already
have alternate keys because IDENTITY should never be the only key of a
table. So if you need to preserve potential duplicates between the two
systems just add another column to make up a compound key. The
additional column identifies the source as "A", "B", "C" or whatever.
Why go to the trouble of putting it into one single column? You can
always concatenate the key as one in a view if you need to.
David Portas
SQL Server MVP
|||An example is worth a thousand words so here's how I would do it.
The two sites, A and B:
CREATE TABLE A_foo (x INTEGER IDENTITY PRIMARY KEY, z CHAR(10) NOT NULL
UNIQUE)
CREATE TABLE A_bar (x INTEGER NOT NULL REFERENCES A_foo (x), k CHAR(10)
NOT NULL, PRIMARY KEY (x,k))
CREATE TABLE B_foo (x INTEGER IDENTITY PRIMARY KEY, z CHAR(10) NOT NULL
UNIQUE)
CREATE TABLE B_bar (x INTEGER NOT NULL REFERENCES B_foo (x), k CHAR(10)
NOT NULL, PRIMARY KEY (x,k))
Generate some sample data:
INSERT INTO A_foo (z)
SELECT 'Alpha' UNION ALL
SELECT 'Beta'
INSERT INTO A_bar (x,k)
SELECT 1,'XXX' UNION ALL
SELECT 1,'YYY' UNION ALL
SELECT 2,'XXX' UNION ALL
SELECT 2,'ZZZ'
INSERT INTO B_foo (z)
SELECT 'Alpha' UNION ALL
SELECT 'Beta'
INSERT INTO B_bar (x,k)
SELECT 1,'111' UNION ALL
SELECT 1,'222' UNION ALL
SELECT 2,'111' UNION ALL
SELECT 2,'333'
These are the two tables for the merged data:
CREATE TABLE foo (x INTEGER IDENTITY PRIMARY KEY, source CHAR(1) NOT
NULL, z CHAR(10) NOT NULL, UNIQUE (source,z))
CREATE TABLE bar (x INTEGER NOT NULL REFERENCES foo (x), k CHAR(10) NOT
NULL, PRIMARY KEY (x,k))
Now do the merge:
INSERT INTO foo (source, z)
SELECT 'A', z
FROM A_foo
UNION ALL
SELECT 'B', z
FROM B_foo
INSERT INTO bar (x,k)
SELECT foo.x, A_bar.k
FROM A_bar
JOIN A_foo
ON A_foo.x = A_bar.x
JOIN foo
ON A_foo.z = foo.z
AND foo.source = 'A'
UNION ALL
SELECT foo.x, B_bar.k
FROM B_bar
JOIN B_foo
ON B_foo.x = B_bar.x
JOIN foo
ON B_foo.z = foo.z
AND foo.source = 'B'
You'll probably want to add a WHERE NOT EXISTS condition to the INSERTs
to ensure that only new data gets loaded.
David Portas
SQL Server MVP