CREATE TABLE with FK and index options declarations

Question

MS SQL Server is giving the following error message on the WITH that preceeds the index options for the dbo.Calendar table: "Incorrect syntax near the word 'WITH'". When the FK declaration is disabled then the error goes away.

CREATE TABLE dbo.Scenario ( ScenarioKey int NOT NULL IDENTITY(1,1), ScenarioName varchar(60) NOT NULL CONSTRAINT [PK-C_dbo.Scenario] PRIMARY KEY CLUSTERED (ScenarioKey) WITH ( PAD_INDEX = OFF, FILLFACTOR = 100, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY] ); GO

Works. But the following fails

CREATE TABLE dbo.Calendar ( ScenarioKey int NOT NULL, Bucket smalldatetime NOT NULL, BucketEnd smalldatetime NOT NULL, CONSTRAINT [PK-C_dbo.Calendar] PRIMARY KEY CLUSTERED (ScenarioKey, Bucket), CONSTRAINT [FK_dbo.Calendar_dbo.Scenario] FOREIGN KEY (ScenarioKey) REFERENCES dbo.Scenario (ScenarioKey) ON DELETE CASCADE ON UPDATE CASCADE WITH ( PAD_INDEX = OFF, FILLFACTOR = 100, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY] ); GO

What is wrong with the syntax?

Martin Smith · Accepted Answer · 2014-01-23 15:11:15Z

The

 WITH ( PAD_INDEX = OFF, /*... */ ALLOW_PAGE_LOCKS = ON )

defines options for the index associated with the PK constraint not the foreign key. So it needs to go as part of the PK constraint definition. You are trying to include it as part of the FK definition. It should be

CREATE TABLE dbo.Calendar ( ScenarioKey INT NOT NULL, Bucket SMALLDATETIME NOT NULL, BucketEnd SMALLDATETIME NOT NULL, CONSTRAINT [PK-C_dbo.Calendar] PRIMARY KEY CLUSTERED (ScenarioKey, Bucket) WITH ( PAD_INDEX = OFF, FILLFACTOR = 100, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON ), CONSTRAINT [FK_dbo.Calendar_dbo.Scenario] FOREIGN KEY (ScenarioKey) REFERENCES dbo.Scenario (ScenarioKey) ON DELETE CASCADE ON UPDATE CASCADE ) ON [PRIMARY]

Collectives™ on Stack Overflow

CREATE TABLE with FK and index options declarations

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related