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 (

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.