January 31, 2010 at 5:51 am
Hi,
I would like to have a primary key which is calculated or comprises of the Servername the database is on + an autonumber (or guid). I would like to have this value inserted by a trigger.
So the tables first three colums would be:
ID (Servername + autonumber) as the PK
Servername
AutoNumber
Is this possible? An if so, can someone please point me in the right direction on how to achieve this?
Thanks
Jeff
January 31, 2010 at 8:49 am
Please note the valid comment below by GilaMonster Posted Today @ 11:33 AM Gail Shaw thank you for bringing my oversight out into the light.
Why use a trigger, you can do what I believe you want to do with a stored procedure. For example:
--Only used for testing purpose
CREATE TABLE Dbo.PkTest(SName VARCHAR(20),SData VARCHAR(30),
AN INT IDENTITY(1,1),PKey VARCHAR(30))
The stored procedure:
CREATE PROCEDURE Dbo.AddData
@Sname VARCHAR(20),
@SData VARCHAR(30)
AS
INSERT INTO Dbo.PkTest(Sname,SData)
VALUES (@Sname,@SData)
UPDATE Dbo.PkTest SET Pkey =@Sname + CAST(Scope_identity() AS VARCHAR(10))
WHERE AN = Scope_identity()
Executed the procedure a few times, produced the folowing:
SName SData AN PKey
NewServerFirst attempt1NewServer1
NextServer2nd attempt2NextServer2
3rdServer3rd attempt33rdServer3
In a simliar fashion you can compose a SP which updates any column in the table to recalculate the primarykey replacing the scope_identity with the identity value in colum I named "AN". Further I believe you would want to include a BEGIN TRANSACTION and a ROLL BACK TRANSACTION if an error occurs or a COMMIT TRANSACTION if no error - iin each SP using this type of coding. That said.
Remember to test, test, test again and when you think you are sure
test again before using in production
January 31, 2010 at 9:33 am
Bitbucket, that proc will fail if the column PKey VARCHAR(30) is actually defined as a PRIMARY KEY. Cannot insert NULL into column will be the error.
Jeff
Why do you want to do this? Technically, a column that comprises a server name and an identity is a violation of 1st normal form, it's a column that does not store atomic values.
What's wrong with an identity column that's defined as a primary key and a server name column that's not null. Or make the combination of the two the pk (though that's not really necessary as the identity is unique on its own)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 31, 2010 at 1:38 pm
Thanks for your replies.
I have a situation where we setup disconnected servers and then merge the disconnected data back into the main db.
We have tried using guids as the Primary Key but had a couple of duplicates across the different data bases. So we are not prepared to do that anymore. I figured that a Auto increment + the Servername would be a guaranteed unique primary key when the data is merged.
I'm open to suggestions, but I need an ID field which is guaranteed to be different across two separate dbs.
Cheers
Jeff
January 31, 2010 at 2:56 pm
Like Gail has suggested, a Primary Key on Servername and AutoNumber would do the trick. I don't think a third column is needed. And you can set the Default Value for the Servername column to be "ServerName" itself.
January 31, 2010 at 9:56 pm
jeff-860557 (1/31/2010)
I'm open to suggestions, but I need an ID field which is guaranteed to be different across two separate dbs.
Two column primary key. ID, ServerName.
No need for a multi-valued column, no need for a trigger.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply