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 |
|
rosetulip
Starting Member
15 Posts |
 Posted - 2013-07-08 : 16:59:23
|
| I have a table with many records. But, there is no any constraints set up in this table so far. There are duplicate records in this table too.Now, I want to add a new column as PK columnI want the identity column as PK field. The current table whose all fields are not qualified for PK field and there are no key in the table. The purpose by using PK field is to prevent duplicate records got inserted into the table.But, the problem is I still cannot prevent duplicate after I set an identity field as PK field. Why? Let me take you my example.Without primary key, I can repeatedly perform this following insert actioninsert into customer (Customer_Name, Dept, Teacher )VALUES ('Suzy', 'CBT', 'John');insert into customer (Customer_Name, Dept, Teacher )VALUES ('Mike', 'CBT', 'John');insert into customer (Customer_Name, Dept, Teacher )VALUES ('Suzy', 'ABC', 'Judy');After I add identity field Customer_ID as primary key to the table, I can still repeatedly perform this above insert action to the database. What I want to do is to prevent a combined duplicate set for field Customer_Name, Dept, Teacher, ie "'Suzy', 'CBT', 'John'" cannot be allow to insert twice.It seems the PK field Customer_ID I added cannot achieve this function. |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-07-08 : 17:07:43
|
| You can create a primary key on multiple columns. In your case, you would create a primary key on Customer_Name, Dept and Teacher. That would then prevent any duplicate entries. Each column in the primary key have to be defined as being non-nullable.One of the downsides to creating a composite primary key like this is that depending on the size of these columns, the key can be "wide". That has negative impact in terms of storage space for indexes etc. But, in your case, if these are names and departments, that should not be an issue.Another possibility is to create an identity column as the primary key and then add a unique constraint (equivalent to a unique index) on the 3 columns. That also will prevent duplicate entrees. |
 |
|
|
rosetulip
Starting Member
15 Posts |
Posted - 2013-07-08 : 17:52:20
|
| This suggestion doesn't work. We have some null values in the fields like Teacher. Also, there are still duplicate records in the old data for 4 fields together.Also, we cannot drop the table and re-create the table since there are so many data store in the table. |
|
|
|
rosetulip
Starting Member
15 Posts |
Posted - 2013-07-08 : 18:07:12
|
| Your second suggestion seems not working too.The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.xxxxxx' and the index name 'IX_xxxx'. The duplicate key value is (1).The statement has been terminated. |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2013-07-08 : 19:00:48
|
| You already have duplicate data. You should removet eh duplicate rows,then create your index/constraint. |
|
|
|
|
|
|
|
|
PK question?