Search code examples
sqlforeign-keyscreate-table

What's the difference between these SQL syntaxes?

I was looking for info on foreign keys.... AGAIN! ... and happened to notice on webschools.com they have different examples of the same thing. for the foreign key example they have

CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)
)

CREATE TABLE Orders
(
O_Id int NOT NULL PRIMARY KEY,
OrderNo int NOT NULL,
P_Id int FOREIGN KEY REFERENCES Persons(P_Id)
)

CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)
)

now..........

what's the difference?...

How do I know which one I'm suppose to use for my database? I have a feeling this will help resolve a lot of the confusion I'm having with SQL...

Solution

  • No difference in effect: They achieve exactly the same thing.

    I prefer the in-line version, because it puts the fk definition as close to the column definition as possible.

    There's a 3rd way - a separate alter table statement (which I think is the "official" way):

    alter table orders
add contraint fk_PerOrders 
foreign key p_id references persons(p_id);

    You may find some databases don't support one version or the other.