Re: Referential integrity using constant in foreign key

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 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to
> majordomo(at)postgresql(dot)org)