Thread: update record with two-column primary key
Using PG 8.0 on Windows, I have a table 'business_list' with a two column primary key. It's a linking table and it's primary keys are the keys from the two tables I am linking: item_id and business. Should I be able to update one of those primary key fields? Here is the SQL: UPDATE projectdata.business_list SET business = 13 Where item_id = 1 and business = 7; It causes the following error: ERROR: duplicate key violates unique constraint "data_business_list_pkey" I need the 'business' value in the WHERE to locate the proper record to update but it also seems to be preventing the update. Is something wrong or is it just not possible to update a two-column primary key? I didn't see any directly relevant info in the forum or documentation. Should I add a surrogate key? That would be disappointing since the existing 2 column PK does satisfy 3rd Normal Form. Thanks in advance for any suggestions! Charles Mortell Advanced Planning Technologies, Inc. Mississippi River port of La Crosse, WI cmortell at apt-cafm dot com
On Nov 12, 2007 10:41 AM, Charles Mortell <cmortell@apt-cafm.com> wrote: > Using PG 8.0 on Windows, I have a table 'business_list' with a two column > primary key. It's a linking table and it's primary keys are the keys from > the two tables I am linking: item_id and business. > Should I be able to update one of those primary key fields? > > Here is the SQL: > UPDATE projectdata.business_list SET business = 13 Where item_id = 1 and > business = 7; > > It causes the following error: ERROR: duplicate key violates unique > constraint "data_business_list_pkey" This is not a complete example. What are the two fields in your pkey? I'm guessing business and list. If list is one, what are the values for list in your table where business=13 and business=7. If you look those up you should see one there with 13 for business and whatever for list that is causing this problem. > Is something wrong or is it just not possible to update a two-column primary > key? Good lord no. You're just making a simple mistake is all. Here, look: create table test (a int, b int, c text, primary key (a,b)) insert into test values (1,2,'abc'); insert into test values (2,2,'abc'); -- Now I update a without checking on b... update test set a=1 where a=2; ERROR: duplicate key violates unique constraint "test_pkey"
Please keep replies on the list, it's more likely to find an answer with many eyes on the problem. On Nov 12, 2007 4:13 PM, Charles Mortell <cmortell@apt-cafm.com> wrote: > Thanks for your response, Scott. > The primary keys of projectdata.business_list are item_id and business. They > are from the two tables I am linking. Item_id is unchanged. With this query: > UPDATE projectdata.business_list SET business = 13 Where item_id = 1 and > business = 7; And this View and rule: > CREATE TABLE projectdata.data_business_list > ( > item_id int4 NOT NULL, > business int4 NOT NULL, > comments varchar(256), > CONSTRAINT data_business_list_pkey PRIMARY KEY (item_id, business) > ) > > CREATE OR REPLACE VIEW projectdata.business_list AS > SELECT t.item_id, t.business, t.comments > FROM projectdata.data_business_list t; > > CREATE OR REPLACE RULE update_buslistview AS > ON UPDATE TO projectdata.business_list DO INSTEAD UPDATE > projectdata.data_business_list > SET item_id = new.item_id, business = new.business, comments = new.comments > WHERE data_business_list.item_id = old.item_id; What is the item_id going to get set to by the update up there? I'm thinking with that query, item_id will be null. > We've tried several variations of the update rule: with and without the > item_id, etc. The 'comments' column does update correctly through the view. Have you tried this: UPDATE projectdata.business_list SET business = 13, item_id=item_id where item_id = 1 and business = 7;
OK, a followup. The problem is that your where clause in your update
rule isn't selective enough, so you're actually trying to update all
the rows that match just the one column in your where clause. Here's
an example:
Note that I've disabled your pk so you can see what's happening:
drop table projectdata.data_business_list cascade;
CREATE TABLE projectdata.data_business_list
(
item_id int4 NOT NULL,
business int4 NOT NULL,
comments varchar(256)
-- , CONSTRAINT data_business_list_pkey PRIMARY KEY (item_id, business)
);
CREATE OR REPLACE VIEW projectdata.business_list AS
SELECT t.item_id, t.business, t.comments
FROM projectdata.data_business_list t;
CREATE OR REPLACE RULE update_buslistview AS
ON UPDATE TO projectdata.business_list DO INSTEAD UPDATE
projectdata.data_business_list
SET business = new.business, item_id=new.item_id, comments = new.comments
WHERE item_id = old.item_id; -- and business=old.business;
insert into projectdata.data_business_list
(item_id, business, comments)
values
(1,2,'abc'), (1,3,'xyz');
UPDATE projectdata.business_list SET business = 13 Where item_id = 1
and business = 2;
select * from projectdata.business_list ;
You'll see the output is this:
item_id | business | comments
---------+----------+----------
1 | 13 | abc
1 | 13 | abc
Note that even the comments are the same. However, if we make your
where clause in your rule more selective, by removing the ; and -- in
the middle of it, and it looks like this:
WHERE item_id = old.item_id and business=old.business;
and run the query again, we get:
select * from projectdata.business_list ;
item_id | business | comments
---------+----------+----------
1 | 3 | xyz
1 | 13 | abc
Now we test it with a real primary key and it also works the same.
Beautiful, Scott. You nailed it. Thanks for the help!
-----Original Message-----
From: Scott Marlowe [mailto:scott.marlowe@gmail.com]
Sent: Monday, November 12, 2007 5:10 PM
To: Charles Mortell
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] update record with two-column primary key
OK, a followup. The problem is that your where clause in your update
rule isn't selective enough, so you're actually trying to update all
the rows that match just the one column in your where clause. Here's
an example:
Note that I've disabled your pk so you can see what's happening:
drop table projectdata.data_business_list cascade;
CREATE TABLE projectdata.data_business_list
(
item_id int4 NOT NULL,
business int4 NOT NULL,
comments varchar(256)
-- , CONSTRAINT data_business_list_pkey PRIMARY KEY (item_id, business)
);
CREATE OR REPLACE VIEW projectdata.business_list AS
SELECT t.item_id, t.business, t.comments
FROM projectdata.data_business_list t;
CREATE OR REPLACE RULE update_buslistview AS
ON UPDATE TO projectdata.business_list DO INSTEAD UPDATE
projectdata.data_business_list
SET business = new.business, item_id=new.item_id, comments = new.comments
WHERE item_id = old.item_id; -- and business=old.business;
insert into projectdata.data_business_list
(item_id, business, comments)
values
(1,2,'abc'), (1,3,'xyz');
UPDATE projectdata.business_list SET business = 13 Where item_id = 1
and business = 2;
select * from projectdata.business_list ;
You'll see the output is this:
item_id | business | comments
---------+----------+----------
1 | 13 | abc
1 | 13 | abc
Note that even the comments are the same. However, if we make your
where clause in your rule more selective, by removing the ; and -- in
the middle of it, and it looks like this:
WHERE item_id = old.item_id and business=old.business;
and run the query again, we get:
select * from projectdata.business_list ;
item_id | business | comments
---------+----------+----------
1 | 3 | xyz
1 | 13 | abc
Now we test it with a real primary key and it also works the same.
Scott Marlowe wrote: Charles Mortell wrote: >> Using PG 8.0 on Windows, I have a table 'business_list' with a two column >> primary key. It's a linking table and it's primary keys are the keys from >> the two tables I am linking: item_id and business. >> Should I be able to update one of those primary key fields? Really it's a mistake to update a primary key. A primary key by definition identifies the row; changing it means to delete the row and insert a new one. Updates are for when you've identified a specific row, that is, a specific primary key. That means that updates are only for dependent columns. What you want to do is literally remove the old record and insert a new one. The duplicate key violation is the purpose of having a primary key, after all. Otherwise you'd create two rows where you should only have one. -- Lew