Uniqueidentifier Philosophy in my scneario

i have a three column table which none of the columns are unique. so I added an Id column to my table as uniqueidentifier and made it primary. Just One Quesion: If two rows with the same values in those columns will be a开发者_JAVA百科dded to the table, what happens to the second one? will it be added or not? How Can I avoid such things in my scenario?

If your three non-ID columns do not have a unique constraint, and are not part of the Primary Key, you can add multiple rows with the exact same values.

If you want to avoid duplicate rows, your best bet would be to apply a unique constraint on at least one or more of the three columns. If you can't do that for some reason, you should post your schema and where you think the problem is to get some more help with it.

If you can't use a unique constraint, one way to avoid duplicates would be: when you're going to insert a record, first check to see if the record already exists, and if it already exists, decide how you want to handle it.

If your goal is to enforce uniqueness across the three columns then you basically have a choice of two structures:

Create Table tbl_1
(
ColA int
,ColB varchar(32)
,ColC varchar(256)
,Primary Key (ColA, ColB, ColC)
)
GO

OR

Create Table tbl_2
(
ID int identity(1,1) Primary Key
,ColA int
,ColB varchar(32)
,ColC varchar(256)
,Unique (ColA, ColB, ColC)
)
GO

There are advantages to either technique, which is better depends on the nature of the data and your interactions with it. However in both structures the values in any given column can repeat but the combination of the three must be unique for each row.

On the other hand if you are just trying to set a PK on data which may or may not be unique then you can use structure 2 but without the unique constraint. In which case each row is uniquely identified by the ID column but the values of the other columns can repeat freely.

The second one will be added. You can avoid it by creating a unique constraint.