| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
12-09-11, 11:55
|
|
Registered User
|
|
Join Date: Dec 2011
Posts: 10
|
|
Multiple foreign keys
|
|
Hi
Can we indicate more than one foreign key in one table?
If the answer is yes, how?
Thanks in advance
|
|
12-09-11, 12:12
|
|
Registered User
|
|
Join Date: Nov 2003
Posts: 2,408
|
|
Code:
create table foo (id integer primary key);
create table bar (id integer primary key);
create table foobar (
foo_id integer not null,
bar_id integer not null,
constraint fk_foobar_foo foreign key (foo_id) references foo (id),
constraint fk_foobar_bar foreign key (bar_id) references bar (id)
);
|
|
12-09-11, 12:28
|
|
Registered User
|
|
Join Date: Dec 2011
Posts: 10
|
|
|
|
Thank you
But I am sorry I didn’t mention can a table have multiple foreign key references just one table not multiple tables?
(just one primary key in one table)
For example:
Table2:
Fk1, Fk2, Fk3 reference table1(PK)
|
Last edited by Asmaa; 12-09-11 at 12:47.
|
12-09-11, 13:43
|
|
Registered User
|
|
Join Date: Nov 2003
Posts: 2,408
|
|
Quote:
Originally Posted by Asmaa
But I am sorry I didn’t mention can a table have multiple foreign key references just one table not multiple tables?
(just one primary key in one table)
For example:
Table2:
Fk1, Fk2, Fk3 reference table1(PK)
|
Sorry, I have no idea what you are asking.
|
|
12-09-11, 17:49
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,540
|
|
Quote:
Originally Posted by Asmaa
For example:
Table2:
Fk1, Fk2, Fk3 reference table1(PK)
|
is this supposed to be asking whether you can have all three of those FKs referncing from table2 to table1?
the answer is yes you can, but i would be very suspicious of that design, and probably look to normalize it, as clearly, FK1, FK2 and FK3 would be different roles (or something) for the same thing
please give a more realistic example with actual data, not this generic stuff
|
|
12-10-11, 03:26
|
|
Registered User
|
|
Join Date: Dec 2011
Posts: 10
|
|
Code:
CREATE TABLE PK
(
FCode VARCHAR NOT NULL PRIMARY KEY,
Type VARCHAR,
VarietyName VARCHAR
);
INSERT INTO PK
(
'A01',
'Apple',
'Pippin'
);
CREATE TABLE FK
(
FCode_1 VARCHAR(I want it to be FOREIGN KEY REFERENCES PK(FCode) and),
FCode_2 VARCHAR(I want it to be FOREIGN KEY REFERENCES PK(FCode) and),
FCode_3 VARCHAR(I want it to be FOREIGN KEY REFERENCES PK(FCode),
someelse VARCHAR
);
Table PK: (have primary key: FCode)
Table FK: (have foreign keys : FCode_1 , FCode _2, FCode_3)
And all foreign key of table FK references the primary key of table PK. //Is it possible?
(and how I can do this?)
Please tell me if I am not clear on this yet!! 
|
|
12-10-11, 03:35
|
|
Registered User
|
|
Join Date: Nov 2003
Posts: 2,408
|
|
I still don't get it, aren't you looking for simple foreign keys:
Code:
CREATE TABLE FK
(
FCode_1 VARCHAR REFERENCES PK(FCode),
FCode_2 VARCHAR REFERENCES PK(FCode),
FCode_3 VARCHAR REFERENCES PK(FCode),
someelse VARCHAR
);
If you want all thre columns to reference the same PK value, that it does not make sense at all (because you only need a single column then)
|
|
12-10-11, 05:52
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,540
|
|
Quote:
Originally Posted by Asmaa
CREATE TABLE PK
(
FCode VARCHAR NOT NULL PRIMARY KEY,
Type VARCHAR,
VarietyName VARCHAR
);
INSERT INTO PK
(
'A01',
'Apple',
'Pippin'
);
|
so far so good, a table about apples
Quote:
Originally Posted by Asmaa
CREATE TABLE FK
(
FCode_1 VARCHAR(I want it to be FOREIGN KEY REFERENCES PK(FCode) and),
FCode_2 VARCHAR(I want it to be FOREIGN KEY REFERENCES PK(FCode) and),
FCode_3 VARCHAR(I want it to be FOREIGN KEY REFERENCES PK(FCode),
someelse VARCHAR
);
|
see, i have a problem with this
you have three columns that reference the apples table, and i think it should be only one, so that there are three rows, not three columns
what's the meaning of 1, 2, 3 here? why 3 and not 4?
are they supposed to represent your favourite three apples in order of preference?
as i said before, yes, this is possible, but it's likely a very poor way of recording whatever it is you're trying to record
imagine the SQL that would be necessary if you wanted to change the priority of your favourite apples from 1, 2, 3, to 3, 1, 2
so... what is it that you're actually trying to record with this table?
|
|
12-10-11, 06:18
|
|
Registered User
|
|
Join Date: Dec 2011
Posts: 10
|
|
Actually it’s a university assignment and I didn’t go through it, I just tried to give an example that makes my question a bit clear. Now I get my answer and it was a point that I didn’t know it, and was about syntax:
We should use:
FCode_1 VARCHAR REFERENCES PK(FCode),
Instead of:
FCode_1 VARCHAR PRIMARY KEY REFERENCES PK(FCode),
This makes a big problem that made me think there is no such a thing at all.
(actually I wanted do something to avoid array)
Thank you both very much it worked.
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
Multiple foreign keys
