#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2006
    Location
    Newcastle, Australia
    Posts
    68
    Rep Power
    9

    SQL and Foreign keys and Data Use


    Can I ask a hypothetical question as I have a gap in my understanding and not sure what to review.

    My Example, I have 3 tables for a meeting agenda.

    TableA is:
    TableAPrimaryKey
    Meetings

    Meetings letters A - J in it

    TableB is:
    TableBPrimaryKey
    Rooms
    Rooms the numbers 1 - 12 in it

    TableC is:
    TableCPrimaryKey
    Name1
    Name2
    Time
    TableAForeignKey - 1 to many
    TableBForeignKey - 1 to many

    Now lets say Table represents a round of meeting and Table B identifies room numbers. I want to enter in meetings.

    So Insert (A, 1, tom, bill, 1pm)
    iNSERT(A, 2, jerry, jane, 2pm)

    and so on.

    But field 1 and field 2 in the insert are the foreignkeys and the data already exists in tableA and table B. it will error if you try to insert.

    How do I insert the meeting data so that it is linked up and combines the meeting, rooms times and people.
    I know could change the table design but I am just trying to understand this facet of SQL in particular.

    Thank You
  2. #2
  3. No Profile Picture
    Lost in code
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 2004
    Posts
    8,316
    Rep Power
    7170
    Maybe I'm not understanding your example, but if you're inserting records with foreign keys those foreign key values are supposed to already exist in the other table before you perform the insert and you won't receive an error.
    PHP FAQ

    Originally Posted by Spad
    Ah USB, the only rectangular connector where you have to make 3 attempts before you get it the right way around
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2006
    Location
    Newcastle, Australia
    Posts
    68
    Rep Power
    9
    In SQL server I receive an error cannot update base tables when doing this.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2006
    Location
    Newcastle, Australia
    Posts
    68
    Rep Power
    9
    It's just got me really confused.
  8. #5
  9. No Profile Picture
    Lost in code
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 2004
    Posts
    8,316
    Rep Power
    7170
    Maybe it's something specific to mssql then, I haven't work with it before so I'm not sure.
    PHP FAQ

    Originally Posted by Spad
    Ah USB, the only rectangular connector where you have to make 3 attempts before you get it the right way around
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,262
    Rep Power
    4279
    i'm moving your thread to the microsoft sql server forum

    it would help if you gave your actual table designs, not fake ones, as well as your actual error message
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2006
    Location
    Newcastle, Australia
    Posts
    68
    Rep Power
    9
    I think I should be inserting with inner join. I haven't stated what the real tables as from prior experience the mere mention of 'Sport' throws people into confusion .

IMN logo majestic logo threadwatch logo seochat tools logo