Re: Referential integrity using constant in foreign key - Mailing list pgsql-general

Is the goal to have code1 always equal 1 and code2 always to equal 2?

If this is your goal and you are trying to ensure no-one enters anything
other than a 1 in code1 or a 2 in code2 is a check constraint what you are
after?

I guess if the 2 columns code1 and code2 have fixed values 1 and 2 it seems
like they might not be even needed?

Not sure if that is what you are asking?

Oisin


----- Original Message -----
From: "Thomas F.O'Connell" <tfo@sitening.com>
To: "Andrus Moor" <nospameetasoftnospam@online.ee>
Cc: <pgsql-general@postgresql.org>
Sent: Monday, March 28, 2005 10:35
Subject: Re: [GENERAL] Referential integrity using constant in foreign key


> Andrus, it's still not clear to me that you're understanding the role
> of referential integrity in database design. It exists to guarantee
> that the values in a column in a given table correspond exactly to the
> values in a column in another table on a per-row basis. It does not
> exist to guarantee that all values in a given column will have a
> specific value.
>
> Referential integrity never dictates the need for "dummy" columns. If
> you have a column that you need to refer to a column in another table
> so strongly that you want the values always to be in sync, you create a
> foreign key, establishing referential integrity between a column (or
> columns) in the table with the foreign key and a column in another
> table (usually a primary key).
>
> I don't understand what you're trying to accomplish well enough to be
> able to make a specific recommendation based on your examples that
> suits your needs.
>
> -tfo
>
> --
> Thomas F. O'Connell
> Co-Founder, Information Architect
> Sitening, LLC
>
> Strategic Open Source — Open Your i™
>
> http://www.sitening.com/
> 110 30th Avenue North, Suite 6
> Nashville, TN 37203-6320
> 615-260-0005
>
> On Mar 25, 2005, at 1:39 PM, Andrus Moor wrote:
>
> > Thomas,
> >
> > thank you for reply. There was a typo in my code. Second table should
> > be
> >
> >  CREATE TABLE info (
> >  code1 CHAR(10),
> >  code2 CHAR(10),
> >  FOREIGN KEY ('1', code1) REFERENCES classifier,
> >  FOREIGN KEY ('2', code2) REFERENCES classifier
> >  );
> >
> > I try to explain my problem more precicely.
> >
> > I can implement the referential integrity which I need in the
> > following way:
> >
> > CREATE TABLE classifier (
> > category CHAR(1),
> > code CHAR(10),
> > PRIMARY KEY (category,code)  );
> >
> > CREATE TABLE info (
> > code1 CHAR(10),
> > code2 CHAR(10),
> > constant1  CHAR default '1',
> > constant2  CHAR default '2',
> > FOREIGN KEY (constant1, code1) REFERENCES classifier,
> > FOREIGN KEY (constant2, code2) REFERENCES classifier
> > );
> >
> > This implementation requires 2 additional columns (constant1 and
> > constant2)
> > which have always same values, '1' and '2' respectively, in all info
> > table
> > rows.
> >
> > I created those dummy columns since Postgres does not allow to write
> > REFERENCES clause like
> >
> > CREATE TABLE info (
> > code1 CHAR(10),
> > code2 CHAR(10),
> > FOREIGN KEY ('1', code1) REFERENCES classifier,
> > FOREIGN KEY ('2', code2) REFERENCES classifier
> > );
> >
> > Is it possible to implement referential integrity without adding
> > additional
> > dummy columns to info table ?
> >
> >> It's somewhat unclear what you're attempting to do, here, but I'll
> >> give a
> >> shot at interpreting. Referential integrity lets you guarantee that
> >> values
> >> in a column or columns exist in a column or columns in another table.
> >>
> >> With classifier as you've defined it, if you want referential
> >> integrity in
> >> the info table, you could do this:
> >>
> >> CREATE TABLE info (
> >> code1 CHAR(10),
> >> code2 CHAR(10),
> >> FOREIGN KEY code1 REFERENCES classifier (category),
> >> FOREIGN KEY code2 REFERENCES classifier (category)
> >> );
> >>
> >> But I'm not sure what you mean by "references to category 1". There is
> >> only a single category column in classifier, and referential
> >> integrity is
> >> not for ensuring that a column in one table contains only values of a
> >> single row.
> >>
> >> Regardless, your syntax doesn't seem to reflect reality. Read the
> >> CREATE
> >> TABLE reference thoroughly.
> >>
> >> http://www.postgresql.org/docs/8.0/static/sql-createtable.html
> >>
> >> -tfo
> >>
> >> --
> >> Thomas F. O'Connell
> >> Co-Founder, Information Architect
> >> Sitening, LLC
> >>
> >> Strategic Open Source — Open Your i™
> >>
> >> http://www.sitening.com/
> >> 110 30th Avenue North, Suite 6
> >> Nashville, TN 37203-6320
> >> 615-260-0005
> >>
> >> On Mar 25, 2005, at 10:22 AM, Andrus Moor wrote:
> >>
> >>> I need to create referential integrity constraints:
> >>>
> >>> CREATE TABLE classifier (
> >>> category CHAR(1),
> >>> code CHAR(10),
> >>> PRIMARY KEY (category,code)  );
> >>>
> >>> -- code1 references to category 1,
> >>> -- code2 references to category 2 from classifier table.
> >>> CREATE TABLE info (
> >>> code1 CHAR(10),
> >>> code2 CHAR(10),
> >>> FOREIGN KEY ('1', category1) REFERENCES classifier,
> >>> FOREIGN KEY ('2', category2) REFERENCES classifier
> >>> );
> >>>
> >>> Unfortunately, second CREATE TABLE causes error
> >>>
> >>> ERROR:  syntax error at or near "'1'" at character 171
> >>>
> >>> Any idea how to implement referential integrity for info table ?
> >>> It seems that this is not possible in Postgres.
> >>>
> >>> Andrus.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>