Re: [GENERAL] 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
[GENERAL] multiple tables as a foreign key
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.
Re: [GENERAL] multiple tables as a foreign key
On 02/20/11 2:40 PM, matty jones wrote: 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. a foreign key reference most certainly can be NULL, unless you've declared it NOT NULL. The correct answer is as many separate attributes as there are different tables you want to reference, some of which might be NULL. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
