Results 1 to 13 of 13
  1. #1
    Join Date
    Feb 2006
    Posts
    18

    Unanswered: regarding creating table using sql w.r.t foreign keys

    suppose there are 6 tables:
    A B C D E F
    A has a primary key , and a foreign key referencing A, and a foreign key referencing B.(?????????)
    B has a primary key, and a foreign key referencing A
    C has a primary key, and a foreign key referencing B
    D has a primary key, and a foreign key referencing C
    E has a primary key, and a foreign key referencing A, and a foreign key that references D
    F has a foreign key that references A.
    A has a foreign key constraint referencing B ...When I try to create the table with the constraints it says table / view B does not exist.?

    WHERE DO I START?

    which table do I create first while mentioning the foreign key constraints?
    Or do I just create the tables first and then mention the foreign key constraints by using the update statement.If so what is the syntax for the UPDATE statement?

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Fire the person who created this monster.
    I challenge you to present an actual business case where these relationships are deemed necessary & appropriate.
    I suspect that this is just a classroom exercise & you should do your own homework.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    1)Create all the tables and primary keys with the foreign constraint disabled.
    2) Load the data
    3) Enable the foreign key constraints.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Enable the foreign key constraints.
    But then how do you get any new records into some/many/most/all of these tables?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    It all depends on what the foreign key is referencing. You can set foreign keys up as a deferred reference, which isn't fired until the commit is done.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  6. #6
    Join Date
    Feb 2006
    Posts
    18

    foreign key constraint please!

    syntax required to add the foreign key constraints using "alter table..... statement.
    table A(aaa primary key ,bbb foreign key references B)
    table B(bbb primary key)

    aaa,bbb are attributes.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    why do you want to alter those tables?

    just declare B first, then A will work fine

    or were you asking to alter B to reference A?

    what actually did you want?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Feb 2006
    Posts
    18

    foreign key constraint.

    Sorry I got my question wrong.

    Table A(primary key a, foreign key aa)
    Table B(primary key bb , foreign key a(refers to 'a' of table A)


    How do I do this....?
    I have created the table first ..then i have applied the primary keys constraints
    then i am trying to apply the foreign key constraints but i am unable get the syntax right.!!
    I need the syntax please prefereably with an example


    well i am new to SQL so please help!!!!!!!

  9. #9
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    Quote Originally Posted by krishna17
    Or do I just create the tables first and then mention the foreign key constraints by using the update statement.If so what is the syntax for the UPDATE statement?
    Not UPDATE, ALTER.

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    please stop creating new threads all over the place

    i'm going to combine them
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    You are either helpless or hopeless and either unwilling or incapable of RTFM found at
    http://tahiti.oracle.com
    or doing a keyword search on
    http://asktom.oracle.com

    As clearly stated as the #1 STICKY post at the top of this forum.

    Here is a free clue:

    SQL> create table A (col1 integer not null, col2 integer, constraint xx
    unique(col1, col2));

    Table created.

    SQL> create table B (col1 integer not null, col2 integer);

    Table created.

    SQL> alter table B add constraint yy foreign key (col1, col2) references A(col1, col2);

    Table altered.

    Now spend the next couple of months reading the SQL Reference Manual found at:

    http://download-west.oracle.com/docs...b14200/toc.htm
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  12. #12
    Join Date
    Feb 2006
    Posts
    18

    To Mr.Anacedent

    Hello Sir,

    You can either help me or please ignore my questions.I have already mentioned in the very beginning that I am new to sql.Its definetly easy once you get the basics, but I am still in the learning process.But please do not insult people who are eager to learn new things.Not all are gifted in grasping things.Every one takes his own time in sorting things out.
    Thank you again for whatever suggestions you have given .I am still going to post questions if they seem stupid to you please ignore them.
    Warm regards.Have a nice weekend Mr.Anacedent and all others in the forum too.
    Bye

    Krishna.

  13. #13
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Please don't take offense at Anacedent. While he/she seems to vent a lot of frustration, Anacedent always provides very good information. Just ignore the comments and read the information. I strongly agree with "RTFM", most of the answers asked on this forum could be found in 15 seconds of searching on Google. However, feel free to ask any question you want. That is why I am a member of the forum, to teach and to learn. Just my two cents worth.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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