CODEWITHSUNDEEP
sqlprimary-key
user609511
user609511

Reputation: 4261

SQL INSERT Violation Primary Keys

I have a table with a primary key composed of three columns: CODE_TARIF, UNITE, MODE_LIV. In my table there are three records with MODE_LIV = 2. Then I insert a record with MODE_LIV = 4.

INSERT INTO T_TARIF (
    CODE_TARIF, ZONE, UNITE, MODE_LIV, LIBELLE, TR_DEBUT, TR_FIN, MONTANT
) 
SELECT 'Livr_A_50_99', '2', '1', '4', 'sdg', '50', '99', '90' 
UNION ALL 
SELECT 'Livr_A_50_99', '2', '1', '4', 'sdg', '50', '99', '90' 
UNION ALL 
SELECT 'Livr_A_50_99', '2', '1', '4', 'sdg', '50', '99', '90'

It returns an error (violation of primary key), which doesn't make any sense because MODE_LIV = 2 is not equal to MODE_LIV = 4.

I know I could add a surrogate key as an auto-incrementing identity column, but in my situation that isn't an option.

Upvotes: 0

Views: 2209

Answers (5)

MatBailie
MatBailie

Reputation: 86706

A primary key is used to identify a single row of data.

In your example you are inserting 3 identical rows of data. This means that the primary key would identify all three rows, not just one. That's a Primary Key violation by definition.

Your options are:
- Add a new field (such as an autoincrementing id) to differentiate between the rows
- Insert just one of the duplicate rows
- Modify your data so that it only requires inserting once (Such as adding a 'count' field)
- Modify your data so that the PK fields are not identical to any other row

Upvotes: 1

StuartLC
StuartLC

Reputation: 107247

If I read your post correctly, you have a composite PK consisting of (CODE_TARIF, UNITE, and MODE_LIV)

In this case, your insert tries to insert all 3 rows with the PK 

'Livr_A_50_99','1', '4'

If you don't intend inserting the exact same data 3 times, then replace your UNION ALL with UNION which will just insert the row once.

If you intend using a surrogate auto incrementing PK instead you will need to change your table DDL to 

CREATE TABLE T_TARIF
(
    T_TARIF_Id INT identity(1,1) NOT NULL,
... Other fields here

)
GO

ALTER TABLE T_TARIF ADD CONSTRAINT PK_Tariff PRIMARY KEY(T_TARIF_Id)
GO

Upvotes: 1

user596075
user596075

Reputation:

I'm guessing by "three primary keys" you mean a composite key. In your case, your primary key cannot have duplicate data, and the composition of CODE_TARIF, UNITE, and MODE_LIV are three duplicate records.

Upvotes: 1

Marco
Marco

Reputation: 57573

Watching your query I see three perfectly equal records you're trying to insert in a table having a primary key (which is violated because three fields building PK are the same 'Livr_A_50_99', '1', '4')!!

Upvotes: 1

Johan
Johan

Reputation: 76557

You are inserting 3 rows with identical data in the primary key fields.
Of course you're going to get PK violations.

Change the code to:

INSERT INTO T_TARIF (CODE_TARIF, ZONE, UNITE, MODE_LIV, LIBELLE, TR_DEBUT, TR_FIN, MONTANT) 
SELECT 'Livr_A_50_99', '2', '1', '3', 'sdg', '50', '99', '90' 
UNION ALL 
SELECT 'Livr_A_50_99', '2', '1', '4', 'sdg', '50', '99', '90' 
UNION ALL 
SELECT 'Livr_A_50_99', '2', '1', '5', 'sdg', '50', '99', '90'

//Different data for every row    ^

Upvotes: 4

Related Questions