To add a primary key constraint to a table as NONCLUSTERED and NOT ENFORCED, which T-SQL statement is correct?

To add a primary key constraint to a table as NONCLUSTERED and NOT ENFORCED, which T-SQL statement is correct?

• A. ALTER TABLE dbo.Dim_Customer add CONSTRAINT PK_Dim_Customer PRIMARY KEY CLUSTERED (CustomerKey) ENFORCED
• B. ALTER TABLE dbo.Dim_Customer add CONSTRAINT PK_Dim_Customer PRIMARY KEY CLUSTERED (CustomerKey) NOT ENFORCED
• C. ALTER TABLE dbo.Dim_Customer add CONSTRAINT PK_Dim_Customer PRIMARY KEY NONCLUSTERED (CustomerKey) ENFORCED
• D. ALTER TABLE dbo.Dim_Customer add CONSTRAINT PK_Dim_Customer PRIMARY KEY NONCLUSTERED (CustomerKey) NOT ENFORCED

Answer: (D)

In SQL Server, you can control both the index type for a primary key and whether the constraint is enforced. A nonclustered primary key uses a nonclustered unique index, and NOT ENFORCED defines the constraint but tells the engine not to enforce it on data (existing or new) until enforcement is turned on again. The statement that matches both requirements is: ALTER TABLE dbo.Dim_Customer ADD CONSTRAINT PK_Dim_Customer PRIMARY KEY NONCLUSTERED (CustomerKey) NOT ENFORCED. This creates a nonclustered primary key constraint that is defined but not enforced. The alternatives would either enforce the constraint, or use a clustered index for the primary key, which isn’t what’s asked.

In SQL Server, you can control both the index type for a primary key and whether the constraint is enforced. A nonclustered primary key uses a nonclustered unique index, and NOT ENFORCED defines the constraint but tells the engine not to enforce it on data (existing or new) until enforcement is turned on again. The statement that matches both requirements is: ALTER TABLE dbo.Dim_Customer ADD CONSTRAINT PK_Dim_Customer PRIMARY KEY NONCLUSTERED (CustomerKey) NOT ENFORCED. This creates a nonclustered primary key constraint that is defined but not enforced. The alternatives would either enforce the constraint, or use a clustered index for the primary key, which isn’t what’s asked.