Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2011
    Posts
    10

    Question Unanswered: Multiple foreign keys

    Hi
    Can we indicate more than one foreign key in one table?
    If the answer is yes, how?
    Thanks in advance

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    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)
    );

  3. #3
    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 13:47.

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by Asmaa View Post
    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.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Asmaa View Post
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    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!!

  7. #7
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    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)

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Asmaa View Post
    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 View Post
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •