Re: Multiple keys?

From: Richard Poole <rp(at)guests(dot)deus(dot)net>
To: pgsql-admin(at)postgresql(dot)org
Subject: Re: Multiple keys?
Date: 2002-08-28 13:36:48
Message-ID: 20020828133648.GA18842@guests.deus.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

On Wed, Aug 28, 2002 at 06:34:55AM -0400, Casey Allen Shobe wrote:
> On Wednesday 28 August 2002 06:22 am, Richard Poole wrote:
> > Your two-column constraint, the one you need to add to get your foreign
> > key working, implies your three-column one. So you can just get rid of
> > the three-column one.
>
> Hmm, that's a problem :\. See I want the table to be constrained by a unique
> combination of the three, but I want the second table to be constrained by a
> unique combination of only the two, as the third column is not present in the
> second table.
>
> I'm uncertain what to do, but I thank you for pointing out my error in
> overconstraining the first table.

You could do it with one more table, perhaps?

CREATE TABLE package_versions (
package_id integer NOT NULL REFERENCES packages,
package_version varchar(16) NOT NULL DEFAULT '0.0.1',
UNIQUE(package_id, package_version)
);

CREATE TABLE package_info (
package_id integer NOT NULL,
package_version varchar(16) NOT NULL,
info_type varchar(32) NOT NULL DEFAULT 'extra_data',
-- other fields
FOREIGN KEY (package_id, package_version) REFERENCES package_versions,
UNIQUE (package_id, package_version, info_type)
);

CREATE TABLE box_packages (
package_id integer NOT NULL,
package_version varchar(16) NOT NULL,
-- other fields
FOREIGN KEY (package_id, package_version) REFERENCES package_versions,
-- other constraints
);

Does that do what you need?

Richard

In response to

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Merlita Maricuelo 2002-08-28 13:56:00 unable to reconnect to database server
Previous Message Merlita Maricuelo 2002-08-28 13:34:35 Re: unable to reconnect to database server