From: David Johnston <polobo@yahoo.com>
To: 'Carlos Mennens' <carlos.mennens@gmail.com>
To: 'PostgreSQL \(SQL\)' <pgsql-sql@postgresql.org>
Subject: Re: Unable To Modify Table
Date: Thu, 12 Jan 2012 11:56:25 -0500
Message-ID: <016a01ccd14b$1f40baf0$5dc230d0$@yahoo.com> (raw)
In-Reply-To: <CAAQLLO70j_j-nz7sZmXbcKT7QRmuf6oQRsCT_N5=+8UC7NFp1A@mail.gmail.com>
References: <CAAQLLO70j_j-nz7sZmXbcKT7QRmuf6oQRsCT_N5=+8UC7NFp1A@mail.gmail.com>
-----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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: polobo@yahoo.com, carlos.mennens@gmail.com
Subject: Re: Unable To Modify Table
In-Reply-To: <016a01ccd14b$1f40baf0$5dc230d0$@yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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