Inserting Into A Table With 2 Pks (1 Viewer)

[MsLady]

The security table is made up of two primary keys: thing, personorgroup
When i run this statement to insert into the security table

INSERT INTO security (thing, personorgroup, accessrights)
SELECT '252600649', '4020', '255'
FROM PROFILE
WHERE not exists(select * from security
where security.thing = '252600649' and security.personorgroup = '4020');

I get this error:
Server: Msg 2627, Level 14, State 1, Line 25
Violation of PRIMARY KEY constraint 'PK_SECURITY'. Cannot insert duplicate key in object 'SECURITY'.
The statement has been terminated.

Anybody know how i can perform my insert successfully?

 

[KeithG]

Sound like you already have a record with the specified primary key. Is this true?

 

[boblarson]

It sure sounds like you already have a combination of

security.thing = '252600649' and security.personorgroup = '4020'

which would cause the violation. Why do you have a compound key?

 

[The_Doc_Man]

The wording of your question makes me pause. I request clarification.

When you say "two primary keys" do you mean...

1. A single primary key with two fields.

2. Two keys with unique indexes as independent fields.

English, being multi-valued, allows me to see either possibility. (If it is #2, you are hosed.)

 

[MsLady]

It sure sounds like you already have a combination of

security.thing = '252600649' and security.personorgroup = '4020'

which would cause the violation. Why do you have a compound key?

tha's what it sounds like but when i run this, it works perfectly and does the insert!

INSERT INTO docsadm.security (thing, personorgroup, accessrights)
values ('252600649', '4020', '255')

the above code works. Meaning i don't have the combination of those values. But what puzzles me is, why do i get that error message when i use the first code i posted.

@Docman: What i mean is "1. A single primary key with two fields." sorry i wasn't clear

 

[MsLady]

why doesn't this work:

INSERT INTO security (thing, personorgroup, accessrights)
SELECT '252600649', '4020', '255'
FROM PROFILE
WHERE not exists(select * from security
where security.thing = '252600649' and security.personorgroup = '4020');

But this works:

INSERT INTO docsadm.security (thing, personorgroup, accessrights)
values ('252600649', '4020', '255')

 

[MsLady]

This doesn't work either

IF NOT EXISTS(select thing, personorgroup, accessrights from docsadm.security
where docsadm.security.thing = '252600649' and docsadm.security.personorgroup = '4038')
INSERT INTO docsadm.security (thing, personorgroup, accessrights)
SELECT '252600649', '4038', '255'
FROM docsadm.PROFILE

This is the PK_security contraint code:
ALTER TABLE [DOCSADM].[SECURITY] ADD CONSTRAINT [PK_SECURITY] PRIMARY KEY CLUSTERED
(
[THING],
[PERSONORGROUP]
) ON [PRIMARY]
GO



retarded error message
Server: Msg 2627, Level 14, State 1, Line 48
Violation of PRIMARY KEY constraint 'PK_SECURITY'. Cannot insert duplicate key in object 'SECURITY'.
The statement has been terminated.

 

[KeithG]

Your problem is because when you add table PROFILE to your from clause you are trying to add a record of '252600649', '4038', '255' for each row returned.

So for example it seems that there is more than one record that exists in Profile that dosen't exist in Security so you are trying to create a record with the same values for each record from Profile that dosen't exist in Security.

 

[MsLady]

Your problem is because when you add table PROFILE to your from clause you are trying to add a record of '252600649', '4038', '255' for each row returned.

So for example it seems that there is more than one record that exists in Profile that dosen't exist in Security so you are trying to create a record with the same values for each record from Profile that dosen't exist in Security.

that's right. what can i do to make this happen?

 

[KeithG]

So you want to create duplicate records in the Security table? If so you need to change your PK. A primary key is used to uniquely identify a record that is why you are getting the error.