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
