CODEWITHSUNDEEP
sqlforeign-keyscreate-table
Optiq
Optiq

Reputation: 3182

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

Upvotes: 2

Views: 91

Answers (3)

Bohemian
Bohemian

Reputation: 424993

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.

Upvotes: 2

Grijesh Chauhan
Grijesh Chauhan

Reputation: 58271

All do same (three ways):

  • In first, you first defined P_Id as int then defined foreign key constraint.

  • In second, P_Id int FOREIGN KEY REFERENCES Persons(P_Id). P_Id is defecation and foreign key constraint defecation in same line.

  • In third, a foreign key constraint name is also give fk_PerOrders. that can be useful later when you wants to drop constraint. e.g.

ALTER TABLE Orders
DROP FOREIGN KEY fk_PerOrders

Its always good practice to give name to a constraint.

Upvotes: 1

techfun
techfun

Reputation: 953

All of them are doing same thing. Use the one which you feel is easy to understand.

Upvotes: 1

Related Questions