Foreign key in SQL Server - which style to use?

Admin 1
Viewed 105

In my database, I have the following code

CREATE TABLE [Users]
(
     userID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
     Username VARCHAR(255),
     Password VARCHAR(255)
);

CREATE TABLE [Image]
(
    ImageID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
    Imagename VARCHAR,
    userID INT,

    FOREIGN KEY(userID) REFERENCES Users (userID)
);

However, here on stackOverflow and multiple other sites, people suggest writing it like this:

CREATE TABLE [Users]
(
    userID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
    Username VARCHAR(255),
    Password VARCHAR(255)
);

CREATE TABLE [Image]
(
    ImageID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
    Imagename VARCHAR,
    userID INT,

    CONSTRAINT fk_Users 
        FOREIGN KEY(userID) REFERENCES Users (userID)
);

I've tried executing both statements, and it seems to do the same..

What am I missing, what's the trick when writing CONSTRAINT fk_Users?

foreign-keys sql-server sql
1 Answers

Writing constraint allows you to name the constraint.

In general, you notice this when the constraint is violated. If you have an opportunity to name the constraint, the error message will make more sense.

For instance, this:

The INSERT statement conflicted with the FOREIGN KEY constraint "fk_image_userId". The conflict occurred in database "rextester", table "dbo.Users", column 'userID'.

The statement has been terminated.

is clearer than this:

The INSERT statement conflicted with the FOREIGN KEY constraint "FK__Image__userID__09211CD4". The conflict occurred in database "rextester", table "dbo.Users", column 'userID'.

The statement has been terminated.

In addition, the constraint name is used in the alter table when you drop or disable the constraint, so having a reasonably named constraint makes that easier.

user_11440350
Related