Re: Merging item codes using referential integrity

From: Thomas F(dot)O'Connell <tfo(at)sitening(dot)com>
To: "Andrus Moor" <nospameetasoftnospam(at)online(dot)ee>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Merging item codes using referential integrity
Date: 2005-03-28 15:41:03
Message-ID: 32e75b827f2f7a0ff9efa20fef47a3a9@sitening.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

If you declare parent.code to be a primary key, you're asserting that
you want it to be unique across all rows in parent. Thus, you will only
ever (be able to) have a single row with a value of 1.

If you do this:

INSERT INTO parent VALUES ('1');
INSERT INTO parent VALUES ('2');
UPDATE parent SET code='1' WHERE code='2';

then the UPDATE will clearly fail because you are trying to create an
additional record with a value of 1 when there already exists a row
with a value of 1 in the column that has been established as a primary
key.

I've only been explaining general database theory and the rules of SQL
in response to your posts because I'm still having a difficult time
understanding what you're trying to accomplish.

-tfo

--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC

Strategic Open Source — Open Your i™

http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-260-0005

On Mar 25, 2005, at 1:23 PM, Andrus Moor wrote:

> I have item table and many child tables where the items are used.
>
> I want to merge two item codes into single item in all tables.
> It is not nice to write a lot of separate UPDATE statements for each
> table.
> So I want to utilize REFERENCES clause for merging.
>
> I tried the following code but got duplicate key error in UPDATE
> statement.
>
> Any idea how to impement this?
>
> CREATE TABLE parent ( code CHAR(10) PRIMARY KEY );
>
> CREATE TABLE orders ( anyfield CHAR(10) REFERENCES parent ON UPDATE
> CASCADE );
> CREATE TABLE invoices ( anyotherfield CHAR(10) REFERENCES parent ON
> UPDATE
> CASCADE );
> -- ... a lot of more child tables with different table and field names
> but -- always with same REFERENCES clause.
>
> INSERT INTO parent VALUES ('1');
> INSERT INTO parent VALUES ('2');
> INSERT INTO orders VALUES ('1');
> INSERT INTO invoices VALUES ('1');
> INSERT INTO orders VALUES ('2');
> INSERT INTO invoices VALUES ('2');
>
> BEGIN;
> -- Direct Postgres to update all child tables. This causes error.
> UPDATE parent SET code='1' WHERE code='2';
> -- Remove duplicate row
> CREATE TABLE parent AS
> SELECT * FROM parent
> GROUP BY CODE ;
> COMMIT;
>
> Andrus.

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Oisin Glynn 2005-03-28 15:46:22 Re: Referential integrity using constant in foreign key
Previous Message Bruno Wolff III 2005-03-28 15:40:05 Re: Tablespaces and indexes