T-SQL Clustered Foreign Key

Question

The "Create Table" grammar rather clearly does not allow me to specify a clustered foreign key constraint. In other words, this is illegal:

--keyword CLUSTERED must be removed before this will execute...
CREATE TABLE [Content](
    [ID] [int] NOT NULL CONSTRAINT PK_Content_ID PRIMARY KEY,
    ContentDefID int NOT NULL CONSTRAINT FK_Plugin_ContentDef FOREIGN KEY CLUSTERED REFERENCES ContentDef(ID)
    )
GO

But I don't understand why it is illegal. ISTM that clustering a foreign-key would facilitate performance of paged-lookups. In other words, "give me child items 80 through 140 of parent ID 20".

Is there a rationale for this?

Update

Based on Oded and Tvanfosson feedback, I've found that the following works:

CREATE TABLE [Content](
    [ID] [int] NOT NULL CONSTRAINT PK_Content_ID PRIMARY KEY,
    ContentDefID int NOT NULL UNIQUE CLUSTERED CONSTRAINT FK_ContentDefContent FOREIGN KEY REFERENCES ContentDef(ID)
    )
GO

But the above causes more problems than it solves. First, a "UNIQUE" foreign key forces my relationship to be one-to-one which I don't want. Second, this only works because it represents the creation of two separate constraints, rather than a single CLUSTERED FOREIGN KEY.

But this investigation is getting me closer to my answer. Evidently clustered indexes MUST be unique, as stated here on SO. Quoting:

If the clustered index is not a unique index, SQL Server makes any duplicate keys unique by adding an internally generated value called a uniqueifier

In particular, I think this answer covers it.

ypercubeᵀᴹ · Accepted Answer · 2012-12-02T10:46:28.833

As others have explained, the clustered index does not have to be the primary key but it either has to be unique or SQL-Server adds a (not shown) UNIQUIFIER column to it.

To avoid this, you can make the clustered index unique by explicitly adding the primary key column to the clustered index, like below. The index will then be avaialbel to be used by the foreign key constraints (and for queries, like joining the two tables).

Notice, that as @Martin Smith has explained, the concepts of CONSTRAINT and INDEX are different. And the various DBMSs implement these in different ways. SQL-Server automatically creates an index for some constraints, while it doesn't for foreign key constraints. It's advised though to have an index that the constraint can use (when deleting or updating in the referenced table):

CREATE TABLE Content(
    ID int NOT NULL,
    ContentDefID int NOT NULL,
    CONSTRAINT PK_Content_ID 
      PRIMARY KEY NONCLUSTERED (ID),
    CONSTRAINT CI_Content
      UNIQUE CLUSTERED (ContentDefID, ID),
    CONSTRAINT FK_Plugin_ContentDef 
      FOREIGN KEY (ContentDefID) REFERENCES ContentDef(ID)
) ;

Martin Smith · Answer 2 · 2012-12-02T10:21:22.950

Is there a rationale for this?

You might as well ask why you can't create a CLUSTERED check constraint or a CLUSTERED default constraint.

A foreign key simply defines a logical constraint and has no indexes automatically created for it in SQL Server (this only happens for UNIQUE or PRIMARY KEY constraints). It is always the case in SQL Server that if you want the FK columns indexed you need to run a CREATE INDEX on the relevant column(s) yourself.

Therefore the concept of a CLUSTERED FOREIGN KEY doesn't make any sense. You can of course create a CLUSTERED INDEX on the columns making up the FK though as you indicate in your question.

score 2 · Answer 3 · edited May 23 '17 at 12:18

2

You can only have one clustered index on a table. By default this will be the primary key column.

There are ways to change this - you will need to use PRIMARY KEY NONCLUSTERED and UNIQUE CLUSTERED FOREIGN KEY.

edited May 23 '17 at 12:18

Community

1
1

answered Dec 01 '12 at 20:44

Oded

489,969
99
883
1,009

1

@ta.speot.is - I believe you can set the `PRIMARY KEY` to `NOT CLUSTERED`. – Oded Dec 01 '12 at 20:46
2

In MSSQL you can use `PRIMARY KEY NONCLUSTERED` when creating the table then create a `UNIQUE CLUSTERED FOREIGN KEY` for the foreign key column – tvanfosson Dec 01 '12 at 20:48
@tvanfosson - Wasn't sure if that was the case. Thanks. – Oded Dec 01 '12 at 20:49
1

@tvanfosson - That creates two different constraints. A `UNIQUE CONSTRAINT` and a `FOREIGN KEY`. – Martin Smith Dec 02 '12 at 10:13

score 0 · Answer 4 · answered Dec 02 '12 at 11:10

It seems you're conflating the ideas of the clustered index with keys (either primary or foreign). Why not just make the table and then specify its clustered index afterwards? (code copied from your first example and changed as little as possible)

CREATE TABLE [Content](
    [ID] [int] NOT NULL CONSTRAINT PK_Content_ID PRIMARY KEY NONCLUSTERED,
    ContentDefID int NOT NULL CONSTRAINT FK_Plugin_ContentDef FOREIGN KEY REFERENCES ContentDef(ID)
    )
GO

CREATE CLUSTERED INDEX IX_Content_Clustered on Content(ContentDefID)

There's no need for you to make the clustered index unique

T-SQL Clustered Foreign Key

Update

4 Answers4