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.
| Author |
Topic |
|
SIRHACK
Starting Member
5 Posts |
 Posted - 2011-02-05 : 21:15:20
|
| Hi,I have a real beginners question.I have a table that has following Definition:ForeID int Don't allow nullP1 nvarchar(30) allow nullP2 nvarchar(30) allow nullP3 nvarchar(30) allow nullP4 nvarchar(30) allow nullForeID is the Primary KeyThe values stored into P1, P2, P3 and P4 should all be unique within the table. Except for "NULL" which is valid all the time in any position.For example:ForeID P1 P2 P3 P41 Bill Carlos Jim James2 NULL Juan Ted NULL3 Jim William Mary Doug4 Dave Pete Sue Clem5 Teddy Julio NULL Hank6 NULL NULL NULL NULLWould be a valid table. However,ForeID P1 P2 P3 P41 Bill Carlos Jim James2 Mike Bill Ted Howard3 Jim William Mary Doug4 Dave Pete Ted Clem5 Teddy Julio NULL Hank6 NULL NULL NULL NULLWould not be valid since "Ted" appears twice as well as "Bill"When performing an INSERT or UPDATE using the below SQL statements I can obviously get duplicates in any row or column or both.UpdateCommand="UPDATE Fours$ SET [P1] = @P1, [P2] = @P2, [P3] = @P3, [P4] = @P4 WHERE ( [FourID] = @FourID) " InsertCommand="INSERT INTO Fours$(P1, P2) VALUES (@P1, @P2)"Is there a way to prevent duplicates from ever getting into the data. This is just in development so no dups exit as yet and I would like to prevent them rather than having to clean out the data once created.Thanks. |
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2011-02-05 : 21:48:21
|
To avoid duplicates you need CONSTRAINTAll Primary Keys are unique constraints. The constraint only applies to the columns specified.In your example, Bill would not violate any constraint, or present a duplicate because it occurs in two different columns. Ted by itself is seen twice, but the ROWS of data are entirely unique (but they do not have to be).quote:
The values stored into P1, P2, P3 and P4 should all be unique within the table. Except for "NULL" which is valid all the time in any position.
Those two statements are not true together as you would only be allowed 1 NULL per COLUMN if a unique constraint were applied on the column. (TWO nulls in one column would violate any unique constraint on that column.In your example, the only enforced constraint is on the COLUMN ForeID, not each ROW where that primary key exists. By definition a primary key is what is used to identify the uniqueness of a row.If you want to enforce unique constraints on the other columns, you could do this by way of a UNIQUE NONCLUSTERED INDEX on each column individually, or the composite of all four. I am not saying you actually would do that in practice however.
Poor planning on your part does not constitute an emergency on my part. |
 |
|
|
SIRHACK
Starting Member
5 Posts |
Posted - 2011-02-05 : 22:40:09
|
| Using UNIQUE NONCLUSTED INDEX on any particular column (i.e., P1, P2, P3, P4) now disallows NULL to appear in multiple rows. Is there any way around this and still have any non-NULL entry be contrained? |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2011-02-06 : 07:17:21
|
No. You can't have a unique index that allows duplicates. You can't have a unique constraint that does that. You would have to specify some kind of default constraint on the column that would always be unique, like the a string of PK + 'missing' or something.
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2011-02-06 : 12:17:05
|
if you changed the way you stored the data this would be easier. Instead of putting names in 4 different columns, put it in ONE column, and have a unique constraint on that. something like this:create table foo( ForeID int not null ,Sequence int not null -- this tells you whether the name is 1st, 2nd, 3rd or 4th in your original table ,FirstName varchar(30) not null unique ,constraint PK_FOO primary key clustered (ForeID,Sequence) )
elsasoft.org |
|
|
|
|
|
|
|
|
Avoiding duplicate data