Skip site navigation (1) Skip section navigation (2)

Re: multi column foreign key for implicitly unique columns

From: Markus Bertheau <twanger(at)bluetwanger(dot)de>
To: olly(at)lfix(dot)co(dot)uk
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: multi column foreign key for implicitly unique columns
Date: 2004-08-17 09:49:57
Message-ID: 1092736197.2627.12.camel@dicaprio.akademie1.de (view raw or flat)
Thread:
Lists: pgsql-sql
В Втр, 17.08.2004, в 11:39, Oliver Elphick пишет:

> What's the point of this?  p.name is the primary key and is therefore
> unique in p, so your foreign key should simply reference p.name.  Having
> f.type as a repetition of p.type violates normalisation principles,
> since name is completely derivable by a join of f to p on name.

The real situation is a little more complicated:

CREATE TABLE classes (
    name TEXT PRIMARY KEY
);
                                                                                                                              
CREATE TABLE class_fields (
    class_name TEXT REFERENCES classes(name),
    field_name TEXT,
    PRIMARY KEY(class_name, field_name)
);
                                                                                                                              
CREATE TABLE objects (
    name TEXT PRIMARY KEY,
    class_name TEXT REFERENCES classes(name)
);
                                                                                                                              
CREATE TABLE object_versions (
    object_name TEXT REFERENCES objects(name),
    object_version DATE,
    PRIMARY KEY(object_name, object_version)
);
                                                                                                                              
CREATE TABLE object_version_property_values (
    object_name TEXT REFERENCES objects(name),
    object_version DATE,
    class_name TEXT,
    field_name TEXT,
    value TEXT,
    FOREIGN KEY(object_name, object_version)
	REFERENCES object_versions(object_name, object_version),
    -- this fk is needed to make sure that the the object in
    -- question really is of the class that field_name is a field of
    FOREIGN KEY(object_name, class_name)
	REFERENCES objects(name, class_name),
    FOREIGN KEY(class_name, field_name)
	REFERENCES class_fields(class_name, field_name)
);
ERROR:  there is no unique constraint matching given keys
	for referenced table "objects"

I need the fk on the columns.

-- 
Markus Bertheau <twanger(at)bluetwanger(dot)de>


In response to

Responses

pgsql-sql by date

Next:From: Richard HuxtonDate: 2004-08-17 09:52:29
Subject: Re: multi column foreign key for implicitly unique columns
Previous:From: Richard HuxtonDate: 2004-08-17 09:48:37
Subject: Re: CROSS-TAB query help? I have read it cant be done in on

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group