CODEWITHSUNDEEP
sqlforeign-keys
lapots
lapots

Reputation: 13395

Differences between "foreign key" and "constraint foreign key"

I mean for example I can create table like

create table XTable
( 
  idt int not null primary key,
  value nvarchar(50),
  idq int,
  constraint fk_idq foreign key(idq) references YTable(idq)
)

and I can create it like this

create table XTable
(
  idt int not null primary key,
  value nvarchar(50),
  idq int,
  foreign key(idq) references YTable(idq)
)

I usually create table like in the second example but now I'm curious about the first example. What is the difference?

Upvotes: 41

Views: 25405

Answers (3)

Adriaan Stander
Adriaan Stander

Reputation: 166346

The first option is purely for naming the constraint.

From SQL FOREIGN KEY Constraint

To allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY constraint on multiple columns, use the following SQL syntax

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

Also, from CREATE TABLE (Transact-SQL) one can see that [ CONSTRAINT constraint_name ] is optional.

Upvotes: 21

RichardTheKiwi
RichardTheKiwi

Reputation: 107696

Apart from controlling the name, nothing really. SQL Server will supply a name if you omit it. FYI, you only need this syntax (SQL Fiddle):

create table XTable
(
  idt int not null primary key,
  value nvarchar(50),
  idq int references YTable(idq)
)

Here's a fuller example.

Upvotes: 5

Lukas Eder
Lukas Eder

Reputation: 220762

The first one assigns a user-defined name to the foreign key, the second one will assign a system-generated name to the foreign key.

User-defined foreign key names can be useful for subsequent statements like these:

ALTER TABLE XTable DROP    CONSTRAINT fk_idq;
ALTER TABLE XTable ENABLE  CONSTRAINT fk_idq;
ALTER TABLE XTable DISABLE CONSTRAINT fk_idq;

It's harder to alter constraints with system-generated names, as you have to discover those names first.

Upvotes: 27

Related Questions