Which command should you run to add a primary key on CustomerKey in dimCustomer, including NOT ENFORCED?

Which command should you run to add a primary key on CustomerKey in dimCustomer, including NOT ENFORCED?

• A. ALTER TABLE dbo.Dim_Customer ADD CONSTRAINT PK_Dim_Customer PRIMARY KEY (CustomerKey) NOT ENFORCED
• B. ALTER TABLE dbo.Dim_Customer ADD PRIMARY KEY (CustomerKey) NONCLUSTERED
• C. CREATE UNIQUE INDEX IF NOT EXISTS PK_Dim_Customer ON dbo.Dim_Customer (CustomerKey)
• D. ALTER TABLE dbo.Dim_Customer add CONSTRAINT PK_Dim_Customer PRIMARY KEY NONCLUSTERED (CustomerKey) NOT ENFORCED

Answer: (D)

The idea is to create a primary key constraint with NOT ENFORCED, and the syntax requires explicitly adding a named constraint, choosing the index type, listing the column, and then attaching NOT ENFORCED. The best choice uses ALTER TABLE to add a named constraint, specifies PRIMARY KEY with NONCLUSTERED, includes the CustomerKey column, and ends with NOT ENFORCED. This matches the proper syntax for creating a not-enforced primary key constraint backed by a nonclustered index. Why the others aren’t correct: one option omits the NONCLUSTERED specification, which leaves the index type unspecified (and defaults may not align with the intent). Another option uses ADD PRIMARY KEY without the constraint name and without NOT ENFORCED. A choice creates a unique index rather than a primary key constraint, which changes the semantics from a constrained key to just an index. The chosen statement combines a named constraint, the PRIMARY KEY clause, the desired NONCLUSTERED index, the column, and the NOT ENFORCED option, making it valid for creating a not-enforced primary key constraint.

The idea is to create a primary key constraint with NOT ENFORCED, and the syntax requires explicitly adding a named constraint, choosing the index type, listing the column, and then attaching NOT ENFORCED.

The best choice uses ALTER TABLE to add a named constraint, specifies PRIMARY KEY with NONCLUSTERED, includes the CustomerKey column, and ends with NOT ENFORCED. This matches the proper syntax for creating a not-enforced primary key constraint backed by a nonclustered index.

Why the others aren’t correct: one option omits the NONCLUSTERED specification, which leaves the index type unspecified (and defaults may not align with the intent). Another option uses ADD PRIMARY KEY without the constraint name and without NOT ENFORCED. A choice creates a unique index rather than a primary key constraint, which changes the semantics from a constrained key to just an index. The chosen statement combines a named constraint, the PRIMARY KEY clause, the desired NONCLUSTERED index, the column, and the NOT ENFORCED option, making it valid for creating a not-enforced primary key constraint.