CODEWITHSUNDEEP
sql-serverperformancet-sqlforeign-keys
Brent Arias
Brent Arias

Reputation: 30165

T-SQL Clustered Foreign Key

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.

Upvotes: 5

Views: 10017

Answers (4)

Ian Yates
Ian Yates

Reputation: 1354

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

Upvotes: 0

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115530

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

Upvotes: 4

Martin Smith
Martin Smith

Reputation: 453037

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.

Upvotes: 4

Oded
Oded

Reputation: 498972

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.

Upvotes: 3

Related Questions