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
