SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 PK question?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rosetulip
Starting Member

15 Posts

Posted - 07/08/2013 :  16:59:23  Show Profile  Reply with Quote
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 column


I 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 action

insert 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
Flowing Fount of Yak Knowledge

3712 Posts

Posted - 07/08/2013 :  17:07:43  Show Profile  Reply with Quote
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.
Go to Top of Page

rosetulip
Starting Member

15 Posts

Posted - 07/08/2013 :  17:52:20  Show Profile  Reply with Quote
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.
Go to Top of Page

rosetulip
Starting Member

15 Posts

Posted - 07/08/2013 :  18:07:12  Show Profile  Reply with Quote
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.
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 07/08/2013 :  19:00:48  Show Profile  Reply with Quote
You already have duplicate data. You should removet eh duplicate rows,then create your index/constraint.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000