Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 13 of 13
  1. #1
    Regular Coder
    Join Date
    Apr 2010
    Posts
    207
    Thanks
    30
    Thanked 0 Times in 0 Posts

    Foreign Key Help

    I am using Microsoft SQL Server Management Studio.

    I created a table called test_db

    CREATE DATABASE test_db

    I then created a table in that DB

    CREATE TABLE Customers
    (
    customerid char(5) primary key,
    address varchar(60) null,
    city char(15) null,
    phone char(24),
    fax char(24) null
    )

    Now I am trying to create a table called Orders, but I am getting a syntax error, and I do not know what I am doing wrong. here is my code.

    CREATE TABLE Orders
    (
    orderid integer primary key,
    customerid integer foreign key (customers.customerid),
    orderdate date null,
    shippeddate date null,
    quantity integer null
    )

    Here is the error (on the "." in the center of (customers.customerid)

    ERROR: Msg 102, Level 15, State 1, Line 4
    Incorrect syntax near '.'
    Website: www.billboardfamily.com
    Twitter: www.twitter.com/billboardfamily
    Facebook Fan Page: www.facebook.com/BillboardFamily
    Facebook (Carl): www.facebook.com/CarlMartin.BillboardFamily (admin note: sig should be <=4 lines)

  • #2
    Master Coder
    Join Date
    Apr 2003
    Location
    in my house
    Posts
    5,211
    Thanks
    39
    Thanked 201 Times in 197 Posts
    dd you forget to show us your query?

    we really need to see it to help with the syntax error.

    bazz
    "The day you stop learning is the day you become obsolete"! - my late Dad.

    Why do some people say "I don't know for sure"? If they don't know for sure then, they don't know!
    Useful MySQL resource
    Useful MySQL link

  • #3
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,633
    Thanks
    4
    Thanked 148 Times in 139 Posts
    Quote Originally Posted by CarlMartin10 View Post
    I am using Microsoft SQL Server Management Studio.
    Are you using this with mysql database? If not then we'll move the thread to the general database forum. This forum is specifically for mysql questions.

  • #4
    Regular Coder
    Join Date
    Apr 2010
    Posts
    207
    Thanks
    30
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by guelphdad View Post
    Are you using this with mysql database? If not then we'll move the thread to the general database forum. This forum is specifically for mysql questions.
    SQL database created from within Microsoft SQL Server Management Studio
    Website: www.billboardfamily.com
    Twitter: www.twitter.com/billboardfamily
    Facebook Fan Page: www.facebook.com/BillboardFamily
    Facebook (Carl): www.facebook.com/CarlMartin.BillboardFamily (admin note: sig should be <=4 lines)

  • #5
    Regular Coder
    Join Date
    Apr 2010
    Posts
    207
    Thanks
    30
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by bazz View Post
    dd you forget to show us your query?

    we really need to see it to help with the syntax error.

    bazz
    That is all the code for this that I used from database creation to this point...all in a NEW QUERY Screen
    Website: www.billboardfamily.com
    Twitter: www.twitter.com/billboardfamily
    Facebook Fan Page: www.facebook.com/BillboardFamily
    Facebook (Carl): www.facebook.com/CarlMartin.BillboardFamily (admin note: sig should be <=4 lines)

  • #6
    Senior Coder tomws's Avatar
    Join Date
    Nov 2007
    Location
    Arkansas
    Posts
    2,644
    Thanks
    29
    Thanked 330 Times in 326 Posts
    Quote Originally Posted by CarlMartin10 View Post
    SQL database created from within Microsoft SQL Server Management Studio
    MySQL is a different database from Microsoft's SQL Server.
    Are you a Help Vampire?

  • #7
    Regular Coder
    Join Date
    Apr 2010
    Posts
    207
    Thanks
    30
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by tomws View Post
    MySQL is a different database from Microsoft's SQL Server.

    I know, I did not see the "Other Databases" category. Please move this post.
    Website: www.billboardfamily.com
    Twitter: www.twitter.com/billboardfamily
    Facebook Fan Page: www.facebook.com/BillboardFamily
    Facebook (Carl): www.facebook.com/CarlMartin.BillboardFamily (admin note: sig should be <=4 lines)

  • #8
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,121
    Thanks
    75
    Thanked 4,338 Times in 4,304 Posts
    Doesn't matter if it is SQL Server, MySQL or Oracle for this one.

    Standard ANSI SQL syntax.

    Code:
    CREATE TABLE Orders
    (
        orderid integer primary key,
        customerid integer foreign key customers(customerid),
        orderdate date null,
        shippeddate date null,
        quantity integer null
    )
    The name of the field goes in *parentheses* after the name of the table.

    You would only use dot notation to specify a table in a different ownership or different database. But that applies only to the table name, not to the field name, in any case.
    Last edited by Old Pedant; 07-27-2010 at 06:39 PM.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #9
    Master Coder
    Join Date
    Apr 2003
    Location
    in my house
    Posts
    5,211
    Thanks
    39
    Thanked 201 Times in 197 Posts
    Sorry CarlMartin10 for not reading your question properly. was very tired and only half of what I read went in.

    bazz
    "The day you stop learning is the day you become obsolete"! - my late Dad.

    Why do some people say "I don't know for sure"? If they don't know for sure then, they don't know!
    Useful MySQL resource
    Useful MySQL link

  • #10
    Regular Coder
    Join Date
    Apr 2010
    Posts
    207
    Thanks
    30
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by bazz View Post
    Sorry CarlMartin10 for not reading your question properly. was very tired and only half of what I read went in.

    bazz
    I understand that, half of my programming issues come from lack of sleep and looking at the code too long. If I take a break, I usually look at the code and think, "WTF was I thinking, here is the answer".

    SQL is BRAND NEW TO ME, so on this one, I really just did not knwo what I was doing wrong.
    Website: www.billboardfamily.com
    Twitter: www.twitter.com/billboardfamily
    Facebook Fan Page: www.facebook.com/BillboardFamily
    Facebook (Carl): www.facebook.com/CarlMartin.BillboardFamily (admin note: sig should be <=4 lines)

  • #11
    Regular Coder
    Join Date
    Apr 2010
    Posts
    207
    Thanks
    30
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    Doesn't matter if it is SQL Server, MySQL or Oracle for this one.

    Standard ANSI SQL syntax.

    Code:
    CREATE TABLE Orders
    (
        orderid integer primary key,
        customerid integer foreign key customers(customerid),
        orderdate date null,
        shippeddate date null,
        quantity integer null
    )
    The name of the field goes in *parentheses* after the name of the table.

    You would only use dot notation to specify a table in a different ownership or different database. But that applies only to the table name, not to the field name, in any case.

    I used your exact code, but I am still getting a syntax error:

    Msg 102, Level 15, State 1, Line 4
    Incorrect syntax near 'CUSTOMERS'.
    Website: www.billboardfamily.com
    Twitter: www.twitter.com/billboardfamily
    Facebook Fan Page: www.facebook.com/BillboardFamily
    Facebook (Carl): www.facebook.com/CarlMartin.BillboardFamily (admin note: sig should be <=4 lines)

  • #12
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,121
    Thanks
    75
    Thanked 4,338 Times in 4,304 Posts
    Whoops! SQL Server is fussy. Requires the REFERENCES keyword. (Funny thing is, if you include REFERENCES then FOREIGN KEY is optional. Reverse is true for MySQL, and I've been working with MySQL for the last 8 months. Sorry!)

    Try:
    Code:
    CREATE TABLE Orders
    (
        orderid integer primary key,
        customerid integer foreign key REFERENCES customers(customerid),
        orderdate date null,
        shippeddate date null,
        quantity integer null
    )
    Look here:
    http://msdn.microsoft.com/en-us/library/ms174979.aspx

    When in doubt, RTFM. I should have done that, myself.

    As you can see, it says [FOREIGN KEY] meaning those words are optional, but REFERENCES is not in [..] so it's required.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #13
    Regular Coder
    Join Date
    Apr 2010
    Posts
    207
    Thanks
    30
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    Whoops! SQL Server is fussy. Requires the REFERENCES keyword. (Funny thing is, if you include REFERENCES then FOREIGN KEY is optional. Reverse is true for MySQL, and I've been working with MySQL for the last 8 months. Sorry!)

    Try:
    Code:
    CREATE TABLE Orders
    (
        orderid integer primary key,
        customerid integer foreign key REFERENCES customers(customerid),
        orderdate date null,
        shippeddate date null,
        quantity integer null
    )
    Look here:
    http://msdn.microsoft.com/en-us/library/ms174979.aspx

    When in doubt, RTFM. I should have done that, myself.

    As you can see, it says [FOREIGN KEY] meaning those words are optional, but REFERENCES is not in [..] so it's required.

    Got it thanks. Had to make the data type the same, but it worked....Thanks!
    Website: www.billboardfamily.com
    Twitter: www.twitter.com/billboardfamily
    Facebook Fan Page: www.facebook.com/BillboardFamily
    Facebook (Carl): www.facebook.com/CarlMartin.BillboardFamily (admin note: sig should be <=4 lines)


  •  

    Posting Permissions

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