Designing unique columns - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Designing unique columns

Author

Topic

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2006-03-07 : 09:49:23

Hi i am designing a database. It will be used by field guys who will insert new records and will replicate at the end of the day with a central database using merge replication. Can anyone tell me if I am choosing wisely or not with the following 3 points:
1. i am going to use a uniqueidentifier value in a column (using newid()) for the merge replication. (I don't care that it's an ugly value as i never plan to look at it.

2. I am using an identity column (INT)to create a unique value within the table

3. I want to create a unique column comprising of data from other columns (e.g. date+identity+salesrep) This gives me an intelligent candidate for a primary key. But I think i have to create this with an instead of trigger (is that right)

many thanks in advance

ICW

Kristen
Test

22859 Posts

Posted - 2006-03-07 : 18:09:17

Hi icw, Welcome to SQL Team!

"But I think i have to create this with an instead of trigger (is that right)"

No, doesn't sound right!

What was your thinking behind needing an instead of trigger?

Kristen

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-03-08 : 10:53:50

...and the Identity column is redundant if you already have your GUID as a surrogate key.

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2006-03-08 : 11:42:48

Sounds like #2 is unnecessary, and #3 chould be supported via a unique index (clustering perhaps, but that depends on your searching and sorting requirements) on the columns you've selected as your candidate key.

For example:


set nocount on

drop table bob
go
create table bob (i uniqueidentifier primary key, a char(1), b char(2))
go
create unique index IX_Bob_CandidateKey
on Bob
(a,b)
go

insert into bob (i,a,b)
select	newid(),'a','b'
union all
select	newid(),'a','bb'

select * from bob

insert into bob (i,a,b)
select newid(),'a','b'

results:


i                                    a    b    
------------------------------------ ---- ---- 
88B5D7A6-4C24-41D7-8420-3309E91E57F5 a    b 
C3C7130F-EDDF-407F-BB1A-1ADE1AF0ACA7 a    bb

Server: Msg 2601, Level 14, State 3, Line 1
Cannot insert duplicate key row in object 'bob' with unique index 'IX_Bob_CandidateKey'.
The statement has been terminated.

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2006-03-09 : 08:19:03

Thanks a lot for your comments.
is the uniqueidentifier generally accepted as a good way to handle replication uniqueness requirements?
thanks
ICW

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-03-09 : 09:49:48

GUIDs are the REQUIRED method of handling replication uniqueness. If your table does not contain a GUID column, one is added when it is configured for replication.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources