Server: Msg 2627??

[SQLnewcommer]

Hello,
I am working on an assignment and I got an error message.
Server: Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK__Supervisor__5A254709'. Cannot insert duplicate key in object 'Supervisor'.
The statement has been terminated.

How do I solve it?

 

[gmmastros]

It looks like you have a supervisor table. It also appears as though there is a primary key on this table.

A primary key is a database constraint (Rule) that prevents duplicates in a table. Primary keys are cool because they prevent problems with data.

To solve this problem, you will need to know which column(s) in your table are the primary key. You can tell by doing this...

In Query Analyzer, drill down to the Supervisor table. You should see 5 folder below it (Columns, indexes, constraints, dependencies, and triggers). Click on contraints, you should see a constraint in there that looks like 'PK__Supervisor__5A254709' Right click on it and the click 'script object to new window -> as create'

A new QA window will appear. it should look like...

ALTER TABLE [dbo].[Supervisor] ADD CONSTRAINT [PK__Supervisor__5A254709] PRIMARY KEY CLUSTERED
(
[[red]FieldName[/red]]
) ON [PRIMARY]
GO

The part in red will be the field name that the primary key is 'protecting'.

Now, look at your insert statement. You are probably trying to insert a record with the same value for the primary key.

I.e. If the primary key is SupervisorId and there is a record in the DB with value 10 and you are trying to insert a record with value 10 for the SupervisorId, then you just found your problem.

Understand? If not, let me know and I will try to clarify more.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLnewcommer]

So you mean if I have something like

PK attribut1 attribute 2
10 abc qrs
20 xyz tpr

I am trying to insert another row like
10 hhg stt?

I just checked my values and I didn't do that. But I did notice that

I have some rows like
PK attribut1 attribute 2
10 abc 10

Is this wrong ?Why is this wrong?

Thanks a lot for your help.

 

[gmmastros]

You can run the following query to see what the primary key is for this table.

sp_pkeys 'supervisor'

Primary Keys are usually set on a table to indicate the unique identifier for a record. You wouldn't want to have 2 records in the same table with the same unique identifier (but representing 2 different things).

> Is this wrong?
It is wrong to insert a new record in a table with a primary key that already exists in the table.

>Why is this wrong?
It's wrong because the Primary Key represents the unique identifier. For something to be unique, by definition, means you cannot have something else exactly like it.

For example, let's say you have a People table. In this imaginary table, you have name, BirthDate, EyeColor, etc... If the Name was the unique identifier (REALLY bad idea), then you wouldn't be able to uniquely identify a single record if there are duplicates. So, you have 2 people in the table with the same name, but representing 2 different people. If you tried to update the EyeColor for one person, both would get updated. You would have incorrect data. Now, suppose, with the same table, NAME was a primary key. You wouldn't be able to insert 2 records (for 2 people) with the same name. The database would prevent it. This keeps your data clean.


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom