Обсуждение: Multiple keys?
In order to get the foreign key I want working, I had to have two unique
references on the parent table, though the second covers two columns that are
contained within the first. Is this wise? Is there a better way?
create table "package_info" (
"info_id" integer not null unique default
nextval('package_info_info_id_seq') primary key,
"package_id" integer not null references "packages" ("package_id"),
"package_version" varchar(16) not null default '0.0.1',
"info_type" varchar(32) not null default 'extra_data',
"info_value" text not null default 'Default',
unique ("package_id", "package_version", "info_type"),
unique ("package_id", "package_version")
);
create table "box_packages" (
"install_id" integer not null unique default
nextval('box_packages_install_id_seq') primary key,
"box_id" integer not null references "boxes" ("box_id"),
"package_id" integer not null,
"package_version" varchar(16) not null,
"install_date" timestamp with time zone not null,
"uninstall_data" timestamp with time zone,
foreign key ("package_id", "package_version") references
"package_info" ("package_id", "package_version"),
unique ("box_id", "package_id", "package_version", "install_date")
);
Danke,
--
Casey Allen Shobe / Network Security Analyst & PHP Developer
SecureWorks, Inc. / 404.327.6339 x169 / Fax: 404.728.0144
cshobe@secureworks.net / http://www.secureworks.net
Content is my own and does not necessarily represent my company.
On Wed, Aug 28, 2002 at 02:10:44AM -0400, Casey Allen Shobe wrote: > In order to get the foreign key I want working, I had to have two unique > references on the parent table, though the second covers two columns that are > contained within the first. Is this wise? Is there a better way? 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. Richard
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. -- Casey Allen Shobe / Network Security Analyst & PHP Developer SecureWorks, Inc. / 404.327.6339 x169 / Fax: 404.728.0144 cshobe@secureworks.net / http://www.secureworks.net Content is my own and does not necessarily represent my company. "Diagoras the Atheist once visited Samothrace and a friend there said to him, 'You think the gods have no care for humans? Why, you can see from all these votive pictures here how many people have escaped the fury of storms at sea by praying to the gods, who have brought them safe to harbour.' 'Yes, indeed,' said Diagoras, 'but where are the pictures of all those who suffered shipwreck and perished in the waves?' On another occasion he was on a voyage and the crew became anxious about the bad weather and began to mutter that it served them right for taking an atheist on board. Diagoras just pointed out to them a number of other ships on the same course which were in equal difficulties and asked them whether they thought that there was a Diagoras on the passenger-list of every one of them. The fact is that a man's character or way of life makes no difference at all to his good luck or bad." - Cicero
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
On Wednesday 28 August 2002 09:36 am, Richard Poole wrote:
> You could do it with one more table, perhaps?
Yes, I actually did it as follows to be for further normalization purposes,
thanks for the tip!
create table "package_versions" (
"version_id" integer not null unique default
nextval('package_versions_version_id_seq') primary key,
"package_id" integer not null references "packages" ("package_id"),
"package_version" varchar(16) not null default '0.0.1',
unique ("package_id", "package_version")
);
create table "package_info" (
"info_id" integer not null unique default
nextval('package_info_info_id_seq') primary key,
"package_version_id" integer not null references "package_versions"
("version_id"),
"info_type" varchar(32) not null default 'extra_data',
"info_value" text not null default 'Default',
unique ("package_version_id", "package_version")
);
create table "box_packages" (
"install_id" integer not null unique default
nextval('box_packages_install_id_seq') primary key,
"box_id" integer not null references "boxes" ("box_id"),
"package_version_id" integer not null references "package_versions"
("version_id"),
"install_date" timestamp with time zone not null,
"uninstall_date" timestamp with time zone not null default '9999-12-31
23:59:59',
unique ("box_id", "package_version_id", "install_date")
);
--
Casey Allen Shobe / Network Security Analyst & PHP Developer
SecureWorks, Inc. / 404.327.6339 x169 / Fax: 404.728.0144
cshobe@secureworks.net / http://www.secureworks.net
Content is my own and does not necessarily represent my company.