Is unique index combined with a "not null" constraint equivalent to a PK constraint?

0 votes

2 answers

113 views

index primary-key constraint azure-sql-database data-integrity

                          I have these legacy table definitions (from here ):

    CREATE TABLE [dbo].[JobItems] (
       [ItemId]            UNIQUEIDENTIFIER NOT NULL,
       -- lots of other columns
       CONSTRAINT [PrimaryKey_GUID_HERE] PRIMARY KEY NONCLUSTERED ([ItemId] ASC)
    );

    CREATE UNIQUE CLUSTERED INDEX [JobItemsIndex]
    ON [dbo].[JobItems]([ItemId] ASC);

which effectively yields two identical indexes. I want to get rid of one of them to reduce insertion time and free up disk space.

The table is stored in a production SQL Azure database, so I cannot rebuild the table and I cannot drop the clustered index (SQL Azure requires a clustered index for every table).

My requirement is I keep all the data integrity guarantees which were provided by the original definition and get rid of the non-clustered index. It looks like having a NOT NULL constraint and a unique index gets me the same guarantees as a PK constraint. So the PK constraint is redundant and I can drop it and the underlying index.

Can I just drop the PK constraint here without expecting anything to break?
                        

Asked by sharptooth (1331 rep)

Aug 10, 2015, 01:58 PM
Last activity: Mar 13, 2020, 10:12 PM

Is unique index combined with a "not null" constraint equivalent to a PK constraint?

Related Questions