C# sql copy rows with 3 primary keys

Question

I am working on an existing table where as I can see there are 3(!) primary keys:

I want to copy the existing rows, alter the ctid column and then copy them again to the end of the table. I try that and I am getting the error:

Cannot add an entity with a key that is already in use.

Probably because I am copying the rows and adding them with the same primary keys. How I can solve this? Is it possible to solve it without modifying the db schema (I am thinking of adding ctid as primary key also)?

Code

var testsDefault = (from i in dc.TestUnits
                                       where i.ctid == null
                                       select i).ToList();

List<DAL.TestUnit> TestList = new List<DAL.TestUnit>();
                    foreach (var test in testsDefault)
                    {
                        DAL.TestUnit newTest = new DAL.TestUnit();
                        newTest.TestID = test.TestID;
                        newTest.PatientType = test.PatientType;
                        newTest.Unit = test.Unit;
                        newTest.ctid = "105";
                        TestList.Add(newTest);
                    }
                    dc.TestUnits.InsertAllOnSubmit(TestList);
                    dc.SubmitChanges();

It's the same as a Unique constraint, only with the clustered index. You need to change at least one of the values. — krillgar, Jun 23 '17 at 14:00
A primary key - composite or not - serves to IDENTIFY a row uniquely. Meaning that if you know the key value(s), then you know the row. You need to think of what is *your* definition of "which value(s) are a suitable combination for indentifying my rows", and then change your program or your table (or even both) accordingly. — Peter B, Jun 23 '17 at 14:08
@krillgar You can have multiple unique constraints on a table. Only one can be clustered. Your statement was simply false — Joe Phillips, Jun 23 '17 at 15:04
@JoePhillips Then you misunderstood my statement. Having a Composite Primary Key is similar to a Unique Key across multiple columns, which most likely will be a clustered index. — krillgar, Jun 23 '17 at 16:42

Joe Phillips · Accepted Answer · 2017-06-23T14:14:14.203

3

You need to add ctid to your composite primary key.

ALTER TABLE TestUnits
DROP CONSTRAINT PK_WhateverYourCompositeIndexNameIs

ALTER TABLE TestUnits
ADD CONSTRAINT PK_WhateverYourCompositeIndexNameIs PRIMARY KEY (TestID, PatientType, Unit, ctid)

See: How can I alter a primary key constraint using SQL syntax?

edited Jun 23 '17 at 14:14

answered Jun 23 '17 at 14:08

Joe Phillips

49,743
32
103
159

can you tell me how I can do this? – aggelos Jun 23 '17 at 14:09
2

Also be aware that changing the primary key could have a wide range of effects that you need to consider (for example, if your code previously expected to get one record by TestID/PatientType/Unit, it might now be getting 1+N records)... Query performance might change negatively, indexes/other queries may need to be reconsidered, etc. – Trioj Jun 23 '17 at 14:16
2

Also a 4-column composite primary key consisting entirely of text columns seems, well, sub-optimal to me. You may also want to consider refactoring to use a surrogate key. – Trioj Jun 23 '17 at 14:24

score 0 · Answer 2 · answered Jun 23 '17 at 14:25

No,it is not possible what you are trying to do without modifying the db schema. Since you are using three PK and Primary key can not be duplicate as you are trying to do. The solution to your problem is make all the row's columns unique and then add the another row but make sure all tuples must have unique entries.

The another solution to your problem is already given by Rafalon

C# sql copy rows with 3 primary keys

2 Answers2