Re: multiple tables as a foreign key

[Sim Zacks <sim@xxxxxxxxxxxxxx>]

On 02/21/2011 12:40 AM, matty jones wrote:

I am not sure if this is possible but is there a way that I can have multiple columns from different tables be a foreign key to a single column in another table, or do I need to write a check function and if so how could I set up a relation?

CREATE TABLE seriestitle (

seriestitle text

);

CREATE TABLE compilationtitle (

compilationtitle text PRIMARY KEY,

pubddate text NOT NULL,

isbn text NOT NULL,

style text REFERENCES style,

storyline text REFERENCES storyline(storyline) DEFAULT '_default_',

seriestitle text REFERENCES seriestitle DEFAULT '_default_',

price text NOT NULL, 

);

CREATE TABLE storytitle (

storytitle text PRIMARY KEY,

notes text DEFAULT '_default_',

);

CREATE TABLE issuetitle (

issuetitle text PRIMARY KEY,

pubdate text NOT NULL,

price text NOT NULL,

bookcover OID REFERENCES bookcover(bookcover),

compilationtitle text REFERENCES compilation(compilation) DEFAULT '_default_',

        seriestitle text REFERENCES seriestitle DEFAULT '_default_',

        );

CREATE TABLE noveltitle (

noveltitle text NOT NULL,

isbn text NOT NULL,

pubdate text NOT NULL,

price text NOT NULL,

bookcover OID REFERENCES bookcover(bookcover),

        seriestitle text REFERENCES seriestitle DEFAULT '_default_',

);

The seriestitle table will contain a list of all the series names that I am collecting but I want to be able to relate them to the issuetitle, compilationtitle, and noveltitle tables.  My thoughts were using a foreign key to do this.  Create an attribute in seriestitle called booktitle and have that be referenced from the other three but that doesn't seem possible or at least I couldn't find out how in the documentation.  If I were to create three separate attributes for each of the separate titles in the seriestitle table then reference those attributes from their respective tables that would produce errors I believe, because a foreign key can't be null and not every attribute will have a value in every tuple.  In reading about normalization a single attribute cannot contain multiple values.  For example:

INSERT INTO issuetitle (seriestitle)

VALUES ('batman, catwoman')

;

Now the seriestitle table would contain 'batman, catwoman' for a value but that would break normalization rules 

The only thing left that I can think of is to create some sort of a function that checks to see whether the value being entered into noveltitle, issuetitle, or compilationtitle is contained within seriestitle but then how would I go about relating a row from one of those three tables to a row in seriestable from a single attribute without having to do a lot of manual work?

Thanks.

First of all you can have a null foreign key. However, in this case that is probably not what you actually want to do.
You probably want the series table with a serial primary key.
You then want the compilation table referencing the series table id.
The stories table will either reference the compilation table or the series table and so on.
You can have multiple table referencing the same key in another table.

Sim