dbTalk Databases Forums  

[SQL] safely exchanging primary keys?

Go Back dbTalk Databases Forums Databases mailing.database.pgsql-sql [SQL] safely exchanging primary keys?

mailing.database.pgsql-sql mailing.database.pgsql-sql


Discuss [SQL] safely exchanging primary keys? in the mailing.database.pgsql-sql forum.



Reply
 
Thread Tools Display Modes
  #1  
Old   
Louis-David Mitterrand
 
Posts: n/a

Default [SQL] safely exchanging primary keys? - 05-24-2010 , 03:51 AM





Hi,

I have this function which swaps primary keys for cabin_types (so that
id_cabin_type ordering reflects natural data ordering):

CREATE OR REPLACE FUNCTION swap_cabin_types(id1 integer, id2 integer) RETURNS integer
AS $$
declare
tmp integer;
begin
tmp := nextval('cabin_type_id_cabin_type_seq');
update cabin_type set id_cabin_type=tmp where id_cabin_type=id1;
update cabin_type set id_cabin_type=id1 where id_cabin_type=id2;
update cabin_type set id_cabin_type=id2 where id_cabin_type=tmp;
return tmp;
end;
$$
LANGUAGE plpgsql;

'id_cabin_type' is a foreign key for two other tables, 'cabin_category'
and 'alert_cabin_type', which have an "on update cascade" clause.

When I run that function it seems the foreign keys are not properly
updated and the data ends up in a mess.

Did I forget something?

Thanks,

--
Sent via pgsql-sql mailing list (pgsql-sql (AT) postgresql (DOT) org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Reply With Quote
  #2  
Old   
Louis-David Mitterrand
 
Posts: n/a

Default Re: [SQL] safely exchanging primary keys? - 05-24-2010 , 09:05 AM





On Mon, May 24, 2010 at 10:51:01AM +0200, Louis-David Mitterrand wrote:
Quote:
Hi,

I have this function which swaps primary keys for cabin_types (so that
id_cabin_type ordering reflects natural data ordering):
Actually this function works fine. My problem was elsewhere. Sorry for
barking up the wrong tree.

--
Sent via pgsql-sql mailing list (pgsql-sql (AT) postgresql (DOT) org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Reply With Quote
  #3  
Old   
Tom Lane
 
Posts: n/a

Default Re: [SQL] safely exchanging primary keys? - 05-24-2010 , 09:22 AM


Louis-David Mitterrand <vindex+lists-pgsql-sql (AT) apartia (DOT) org> writes:
Quote:
When I run that function it seems the foreign keys are not properly
updated and the data ends up in a mess.
Yeah? Could we see an actual example of what you're talking about?
And which PG version is this?

regards, tom lane

--
Sent via pgsql-sql mailing list (pgsql-sql (AT) postgresql (DOT) org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Reply With Quote
  #4  
Old   
Rob Sargent
 
Posts: n/a

Default Re: [SQL] safely exchanging primary keys? - 05-24-2010 , 09:22 AM


And relying on keys for a sort order is a very wrong tree

On 05/24/2010 08:05 AM, Louis-David Mitterrand wrote:
Quote:
On Mon, May 24, 2010 at 10:51:01AM +0200, Louis-David Mitterrand wrote:
Hi,

I have this function which swaps primary keys for cabin_types (so that
id_cabin_type ordering reflects natural data ordering):

Actually this function works fine. My problem was elsewhere. Sorry for
barking up the wrong tree.
--
Sent via pgsql-sql mailing list (pgsql-sql (AT) postgresql (DOT) org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Reply With Quote
  #5  
Old   
Tim Landscheidt
 
Posts: n/a

Default Re: [SQL] safely exchanging primary keys? - 05-24-2010 , 09:38 AM


Louis-David Mitterrand <vindex+lists-pgsql-sql (AT) apartia (DOT) org> wrote:

Quote:
I have this function which swaps primary keys for cabin_types (so that
id_cabin_type ordering reflects natural data ordering):

CREATE OR REPLACE FUNCTION swap_cabin_types(id1 integer, id2 integer) RETURNS integer
AS $$
declare
tmp integer;
begin
tmp := nextval('cabin_type_id_cabin_type_seq');
update cabin_type set id_cabin_type=tmp where id_cabin_type=id1;
update cabin_type set id_cabin_type=id1 where id_cabin_type=id2;
update cabin_type set id_cabin_type=id2 where id_cabin_type=tmp;
return tmp;
end;
$$
LANGUAGE plpgsql;

'id_cabin_type' is a foreign key for two other tables, 'cabin_category'
and 'alert_cabin_type', which have an "on update cascade" clause.

When I run that function it seems the foreign keys are not properly
updated and the data ends up in a mess.

Did I forget something?
What does "are not properly updated" mean? Anyhow, why don't
you use something simple like (untested):

Quote:
UPDATE cabin_type
SET id_cabin_type =
CASE
WHEN id_cabin_type = id1 THEN
id2
ELSE
id1
END
WHERE id_cabin_type IN (id1, id2);
Tim


--
Sent via pgsql-sql mailing list (pgsql-sql (AT) postgresql (DOT) org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Reply With Quote
  #6  
Old   
Louis-David Mitterrand
 
Posts: n/a

Default Re: [SQL] safely exchanging primary keys? - 05-24-2010 , 12:00 PM


On Mon, May 24, 2010 at 02:38:39PM +0000, Tim Landscheidt wrote:
Quote:
Louis-David Mitterrand <vindex+lists-pgsql-sql (AT) apartia (DOT) org> wrote:

What does "are not properly updated" mean? Anyhow, why don't
Hi,

I did follow-up on my own post: the problem was elsewhere.

Quote:
you use something simple like (untested):

| UPDATE cabin_type
| SET id_cabin_type =
| CASE
| WHEN id_cabin_type = id1 THEN
| id2
| ELSE
| id1
| END
| WHERE id_cabin_type IN (id1, id2);
Nice, thanks.

--
Sent via pgsql-sql mailing list (pgsql-sql (AT) postgresql (DOT) org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Reply With Quote
  #7  
Old   
Louis-David Mitterrand
 
Posts: n/a

Default Re: [SQL] safely exchanging primary keys? - 05-24-2010 , 12:11 PM


On Mon, May 24, 2010 at 07:00:30PM +0200, Louis-David Mitterrand wrote:
Quote:
On Mon, May 24, 2010 at 02:38:39PM +0000, Tim Landscheidt wrote:
you use something simple like (untested):

| UPDATE cabin_type
| SET id_cabin_type =
| CASE
| WHEN id_cabin_type = id1 THEN
| id2
| ELSE
| id1
| END
| WHERE id_cabin_type IN (id1, id2);

Nice, thanks.
Ah, but this won't work as the UNIQUE PK constraint is in force.

--
Sent via pgsql-sql mailing list (pgsql-sql (AT) postgresql (DOT) org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Reply With Quote
  #8  
Old   
Tim Landscheidt
 
Posts: n/a

Default Re: [SQL] safely exchanging primary keys? - 05-24-2010 , 08:22 PM


Louis-David Mitterrand <vindex+lists-pgsql-sql (AT) apartia (DOT) org> wrote:

Quote:
you use something simple like (untested):

| UPDATE cabin_type
| SET id_cabin_type =
| CASE
| WHEN id_cabin_type = id1 THEN
| id2
| ELSE
| id1
| END
| WHERE id_cabin_type IN (id1, id2);

Nice, thanks.

Ah, but this won't work as the UNIQUE PK constraint is in force.
Oh, yes, you're right, I didn't have that premise in mind.

Tim


--
Sent via pgsql-sql mailing list (pgsql-sql (AT) postgresql (DOT) org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Reply With Quote
Reply

« Previous Thread | Next Thread »



Thread Tools
Display Modes
Linear Mode Linear Mode

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is On
HTML code is Off



Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2013, Jelsoft Enterprises Ltd.


Contact Us - dbTalk Databases Forums - Archive - Top