pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedUnable To Modify Table
7+ messages / 5 participants
[nested] [flat]
* Unable To Modify Table
@ 2012-01-12 16:42 Carlos Mennens <carlos.mennens@gmail.com>
2012-01-12 16:53 ` Re: Unable To Modify Table Steve Crawford <scrawford@pinpointresearch.com>
2012-01-12 16:54 ` Re: Unable To Modify Table Adrian Klaver <adrian.klaver@gmail.com>
2012-01-12 16:56 ` Re: Unable To Modify Table David Johnston <polobo@yahoo.com>
0 siblings, 3 replies; 7+ messages in thread
From: Carlos Mennens @ 2012-01-12 16:42 UTC (permalink / raw)
To: pgsql-sql
I seem to have an issue where I can't modify a table due to another
tables foreign key association:
[CODE]trinity=# \d developers
Table "public.developers"
Column | Type | Modifiers
--------------+----------------+-----------
id | character(10) | not null
name | character(50) | not null
address | character(50) |
city | character(50) |
state | character(2) |
zip | character(10) |
country | character(50) |
phone | character(50) |
email | character(255) |
Indexes:
"developers_pkey" PRIMARY KEY, btree (id)
Referenced by:
TABLE "orders" CONSTRAINT "fk_orders_developers" FOREIGN KEY (id)
REFERENCES developers(id)
[/CODE]
Now I want to change the formatting of field data in 'id' in table 'developers':
[CODE]trinity=# SELECT id FROM developers;
id
------------
1000000001
1000000002
1000000003
1000000004
1000000005
1000000006
(109 rows)
[/CODE]
Now when I try and change the values before I alter the field TYPE, I
get an error that another table (orders) with a foreign key associated
with public.developers 'id' field still has old values therefor can't
change / modify the 'developers' table.
[CODE]trinity=# UPDATE developers SET id = '1000' WHERE id = '1000000001';
ERROR: update or delete on table "developers" violates foreign key
constraint "fk_orders_developers" on table "orders"
DETAIL: Key (id)=(1000000001) is still referenced from table "orders".
[/CODE]
How does one accomplish my goal? Is this difficult to change or once
that foreign key is created, are you stuck with that particular
constraint?
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Unable To Modify Table
2012-01-12 16:42 Unable To Modify Table Carlos Mennens <carlos.mennens@gmail.com>
@ 2012-01-12 16:53 ` Steve Crawford <scrawford@pinpointresearch.com>
2 siblings, 0 replies; 7+ messages in thread
From: Steve Crawford @ 2012-01-12 16:53 UTC (permalink / raw)
To: Carlos Mennens <carlos.mennens@gmail.com>; +Cc: pgsql-sql
On 01/12/2012 08:42 AM, Carlos Mennens wrote:
> I seem to have an issue where I can't modify a table due to another
> tables foreign key association:
>
> ...
>
> How does one accomplish my goal? Is this difficult to change or once
> that foreign key is created, are you stuck with that particular
> constraint?
>
Try updating the values in both tables within a transaction with
constraints set to deferred:
http://www.postgresql.org/docs/current/static/sql-set-constraints.html
Cheers,
Steve
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Unable To Modify Table
2012-01-12 16:42 Unable To Modify Table Carlos Mennens <carlos.mennens@gmail.com>
@ 2012-01-12 16:54 ` Adrian Klaver <adrian.klaver@gmail.com>
2012-01-12 17:02 ` Re: Unable To Modify Table David Johnston <polobo@yahoo.com>
2 siblings, 1 reply; 7+ messages in thread
From: Adrian Klaver @ 2012-01-12 16:54 UTC (permalink / raw)
To: pgsql-sql; +Cc: Carlos Mennens <carlos.mennens@gmail.com>
On Thursday, January 12, 2012 8:42:59 am Carlos Mennens wrote:
> I seem to have an issue where I can't modify a table due to another
> tables foreign key association:
>
> [CODE]trinity=# \d developers
> Table "public.developers"
> Column | Type | Modifiers
> --------------+----------------+-----------
> id | character(10) | not null
> name | character(50) | not null
> address | character(50) |
> city | character(50) |
> state | character(2) |
> zip | character(10) |
> country | character(50) |
> phone | character(50) |
> email | character(255) |
> Indexes:
> "developers_pkey" PRIMARY KEY, btree (id)
> Referenced by:
> TABLE "orders" CONSTRAINT "fk_orders_developers" FOREIGN KEY (id)
> REFERENCES developers(id)
> [/CODE]
>
>
> Now when I try and change the values before I alter the field TYPE, I
> get an error that another table (orders) with a foreign key associated
> with public.developers 'id' field still has old values therefor can't
> change / modify the 'developers' table.
>
> [CODE]trinity=# UPDATE developers SET id = '1000' WHERE id = '1000000001';
> ERROR: update or delete on table "developers" violates foreign key
> constraint "fk_orders_developers" on table "orders"
> DETAIL: Key (id)=(1000000001) is still referenced from table "orders".
> [/CODE]
>
> How does one accomplish my goal? Is this difficult to change or once
> that foreign key is created, are you stuck with that particular
> constraint?
You are pushing in the wrong direction. You need to make the change in the table
'orders'. This assumes the FK in 'orders' has ON UPDATE CASCADE enabled.
--
Adrian Klaver
adrian.klaver@gmail.com
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Unable To Modify Table
2012-01-12 16:42 Unable To Modify Table Carlos Mennens <carlos.mennens@gmail.com>
2012-01-12 16:54 ` Re: Unable To Modify Table Adrian Klaver <adrian.klaver@gmail.com>
@ 2012-01-12 17:02 ` David Johnston <polobo@yahoo.com>
2012-01-12 17:10 ` Re: Unable To Modify Table Adrian Klaver <adrian.klaver@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: David Johnston @ 2012-01-12 17:02 UTC (permalink / raw)
To: 'Adrian Klaver' <adrian.klaver@gmail.com>; pgsql-sql; +Cc: 'Carlos Mennens' <carlos.mennens@gmail.com>
-----Original Message-----
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]
On Behalf Of Adrian Klaver
Sent: Thursday, January 12, 2012 11:55 AM
To: pgsql-sql@postgresql.org
Cc: Carlos Mennens
Subject: Re: [SQL] Unable To Modify Table
>
> How does one accomplish my goal? Is this difficult to change or once
> that foreign key is created, are you stuck with that particular
> constraint?
You are pushing in the wrong direction. You need to make the change in the
table 'orders'. This assumes the FK in 'orders' has ON UPDATE CASCADE
enabled.
----------------------------------------
Adrian, you are not helping...if ON UPDATE CASCADE was enabled on "orders"
the error in question would never have appeared and the UPDATE would have
succeeded. Carlos' goal is to change the value of a Primary Key that has
already been used in a FOREIGN KEY constraint and he needs to learn to use
the documentation to solve some of these basic questions instead of asking
the list. His approach is correct, execute UPDATE against the "developers"
table.
Deferrable constraints and transactions work as well but are more
complicated to setup and execute compared to the more direct ON UPDATE
CASCADE modifier to the FOREIGN KEY. But learning both methods is good.
David J.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Unable To Modify Table
2012-01-12 16:42 Unable To Modify Table Carlos Mennens <carlos.mennens@gmail.com>
2012-01-12 16:54 ` Re: Unable To Modify Table Adrian Klaver <adrian.klaver@gmail.com>
2012-01-12 17:02 ` Re: Unable To Modify Table David Johnston <polobo@yahoo.com>
@ 2012-01-12 17:10 ` Adrian Klaver <adrian.klaver@gmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: Adrian Klaver @ 2012-01-12 17:10 UTC (permalink / raw)
To: David Johnston <polobo@yahoo.com>; +Cc: pgsql-sql@postgresql.org, "'Carlos Mennens'" <carlos.mennens@gmail.com>
On Thursday, January 12, 2012 9:02:35 am David Johnston wrote:
> ----------------------------------------
>
> Adrian, you are not helping...if ON UPDATE CASCADE was enabled on "orders"
> the error in question would never have appeared and the UPDATE would have
> succeeded. Carlos' goal is to change the value of a Primary Key that has
> already been used in a FOREIGN KEY constraint and he needs to learn to use
> the documentation to solve some of these basic questions instead of asking
> the list. His approach is correct, execute UPDATE against the
> "developers" table.
My mistake, I got the table relationship order wrong. Sorry for the noise.
>
> David J.
--
Adrian Klaver
adrian.klaver@gmail.com
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Unable To Modify Table
2012-01-12 16:42 Unable To Modify Table Carlos Mennens <carlos.mennens@gmail.com>
@ 2012-01-12 16:56 ` David Johnston <polobo@yahoo.com>
2012-01-14 08:20 ` Re: Unable To Modify Table Gavin Flower <GavinFlower@archidevsys.co.nz>
2 siblings, 1 reply; 7+ messages in thread
From: David Johnston @ 2012-01-12 16:56 UTC (permalink / raw)
To: 'Carlos Mennens' <carlos.mennens@gmail.com>; pgsql-sql
-----Original Message-----
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]
On Behalf Of Carlos Mennens
Sent: Thursday, January 12, 2012 11:43 AM
To: PostgreSQL (SQL)
Subject: [SQL] Unable To Modify Table
I seem to have an issue where I can't modify a table due to another tables
foreign key association:
[CODE]trinity=# \d developers
Table "public.developers"
Column | Type | Modifiers
--------------+----------------+-----------
id | character(10) | not null
name | character(50) | not null
address | character(50) |
city | character(50) |
state | character(2) |
zip | character(10) |
country | character(50) |
phone | character(50) |
email | character(255) |
Indexes:
"developers_pkey" PRIMARY KEY, btree (id) Referenced by:
TABLE "orders" CONSTRAINT "fk_orders_developers" FOREIGN KEY (id)
REFERENCES developers(id) [/CODE]
Now I want to change the formatting of field data in 'id' in table
'developers':
[CODE]trinity=# SELECT id FROM developers;
id
------------
1000000001
1000000002
1000000003
1000000004
1000000005
1000000006
(109 rows)
[/CODE]
Now when I try and change the values before I alter the field TYPE, I get an
error that another table (orders) with a foreign key associated with
public.developers 'id' field still has old values therefor can't change /
modify the 'developers' table.
[CODE]trinity=# UPDATE developers SET id = '1000' WHERE id = '1000000001';
ERROR: update or delete on table "developers" violates foreign key
constraint "fk_orders_developers" on table "orders"
DETAIL: Key (id)=(1000000001) is still referenced from table "orders".
[/CODE]
How does one accomplish my goal? Is this difficult to change or once that
foreign key is created, are you stuck with that particular constraint?
---------------------------------------------------------------
There are two possible actions you can take with respect to an existing
Primary Key; you can UPDATE it or you can DELETE it. When you define a
FOREIGN KEY you can specify what you want to happen if the corresponding
PRIMARY KEY is UPDATEd or DELETEd. Read the documentation on FOREIGN KEY in
detail to understand why you are seeing that error and what modifications
you can make to the FOREIGN KEY on "orders" to obtain different behavior.
Keep in mind, also, that the TYPE of the PRIMARY KEY and FOREIGN KEY must
match.
Contrary to my earlier advice assigning a sequential ID (thus using a
numeric TYPE) is one of the exceptions where you can use a number even
though you cannot meaningfully perform arithmetic on the values. The reason
you would use a numeric value instead of a character is that the value
itself is arbitrary and the space required to store a number is less than
the space required to store a string of the same length.
There are many points-of-view regarding whether to use "serial" PRIMARY KEYs
but regardless of whether you add one or not you should try and define a
UNIQUE constraint on the table by using meaningful values. However, for
things like Orders this is generally not possible and so you would want to
generate a sequential identifier for every record.
David J.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Unable To Modify Table
2012-01-12 16:42 Unable To Modify Table Carlos Mennens <carlos.mennens@gmail.com>
2012-01-12 16:56 ` Re: Unable To Modify Table David Johnston <polobo@yahoo.com>
@ 2012-01-14 08:20 ` Gavin Flower <GavinFlower@archidevsys.co.nz>
0 siblings, 0 replies; 7+ messages in thread
From: Gavin Flower @ 2012-01-14 08:20 UTC (permalink / raw)
To: David Johnston <polobo@yahoo.com>; +Cc: 'Carlos Mennens' <carlos.mennens@gmail.com>; pgsql-sql
On 13/01/12 05:56, David Johnston wrote:
> [...]
> Contrary to my earlier advice assigning a sequential ID (thus using a
> numeric TYPE) is one of the exceptions where you can use a number even
> though you cannot meaningfully perform arithmetic on the values. The reason
> you would use a numeric value instead of a character is that the value
> itself is arbitrary and the space required to store a number is less than
> the space required to store a string of the same length.
>
> There are many points-of-view regarding whether to use "serial" PRIMARY KEYs
> but regardless of whether you add one or not you should try and define a
> UNIQUE constraint on the table by using meaningful values. However, for
> things like Orders this is generally not possible and so you would want to
> generate a sequential identifier for every record.
>
> David J.
>
>
Hmm...
In any database I design, I deliberately keep primary keys quite
separate from any user visible values. In order to minimise changes to
the database resulting from business format changes, such as redoing the
format of customer numbers for marketing purposes.
Also, in a chain of parent child tables, the child only needs to know
how to get its parent, it does not need to know its grandparents! One
insurance package I worked on, had the primary key of a child table a
concatenation of its parent's primary key with a unique field. So some
child tables had multiple character field as their primary keys,
potentially have keys of some 45 or more characters!
I normally use integers for the primary key type. This makes keeping
track of records in a program much easier.
However, I do not usually expose these keys to users, and it would be
rare (if ever) to have them as fields in search boxes.
Cheers,
Gavin
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2012-01-14 08:20 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-01-12 16:42 Unable To Modify Table Carlos Mennens <carlos.mennens@gmail.com>
2012-01-12 16:53 ` Steve Crawford <scrawford@pinpointresearch.com>
2012-01-12 16:54 ` Adrian Klaver <adrian.klaver@gmail.com>
2012-01-12 17:02 ` David Johnston <polobo@yahoo.com>
2012-01-12 17:10 ` Adrian Klaver <adrian.klaver@gmail.com>
2012-01-12 16:56 ` David Johnston <polobo@yahoo.com>
2012-01-14 08:20 ` Gavin Flower <GavinFlower@archidevsys.co.nz>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox