Re: multiple tables as a foreign key

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 (
> seriestitletext
> );
> CREATE TABLE compilationtitle (
> compilationtitletextPRIMARY KEY,
> pubddatetextNOT NULL,
> isbntextNOT NULL,
> styletextREFERENCES style,
> storylinetextREFERENCES storyline(storyline) DEFAULT '_default_',
> seriestitletextREFERENCES seriestitle DEFAULT '_default_',
> pricetextNOT NULL,
> );
>
> CREATE TABLE storytitle (
> storytitletextPRIMARY KEY,
> notestextDEFAULT '_default_',
> );
>
> CREATE TABLE issuetitle (
> issuetitletextPRIMARY KEY,
> pubdatetextNOT NULL,
> pricetextNOT NULL,
> bookcoverOIDREFERENCES bookcover(bookcover),
> compilationtitletextREFERENCES compilation(compilation) DEFAULT
> '_default_',
> seriestitletextREFERENCES seriestitle DEFAULT '_default_',
> );
> CREATE TABLE noveltitle (
> noveltitletextNOT NULL,
> isbntextNOT NULL,
> pubdatetextNOT NULL,
> pricetextNOT NULL,
> bookcoverOIDREFERENCES bookcover(bookcover),
> seriestitletextREFERENCES 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