CODEWITHSUNDEEP
sqlsql-serverforeign-keys
Mark
Mark

Reputation: 347

SQL Server Different ways of creating table with foreign key

I need to create table with a foreign key. So far I have been doing that like this:

CREATE TABLE books 
(
   book_id NVARCHAR(15) NOT NULL UNIQUE,
   author_id INT REFERENCES authors(author_id)
   ... 
);

But my professor from university sent me exemplary scripts showing another way of dealing with foreign keys:

CREATE TABLE books 
(
   book_id NVARCHAR(15) NOT NULL UNIQUE,
   author_id INT,

   CONSTRAINT author_FK 
       FOREIGN KEY(author_id) REFERENCES authors(author_id)
   ...
);

Trying to find the difference between those, I made a research. Unfortunately I haven't found the answer, what I found was another way of creating table with foreign key (very similar to the second one):

CREATE TABLE books 
(
   book_id NVARCHAR(15) NOT NULL UNIQUE,
   author_id INT,
   FOREIGN KEY(author_id) REFERENCES authors(author_id)
   ... 
);

Could you point out the differences between all of them?

Upvotes: 5

Views: 2699

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

Functionally, there is no difference between the two. The first is called an inline constraint (and can be used for check constraints as well).

There are two minor difference. The first is that the constraint keyword is not necessary of the inline reference, so inline references often do not name the constraint (constraint is allowed and you can name the reference, but that is not the syntax you show).

The second is that the foreign key reference can only use one column. For me, this is almost never an issue, because I almost always have synthetic primary keys rather than composite primary keys. However, the inline syntax is less powerful than the full constraint definition.

By the way, there is a third method which uses alter table. This is similar to your second method, but it allows for constraints to be added after a table has already been created.

Upvotes: 6

Related Questions