Re: Multiple keys?

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