Disabling Primary Key/ Clustered index

Most of us are aware that indexes and constraints can be disabled. This post will concentrate on what happens when a Clustered index or Primary key is disabled.

When a Clustered index is disabled , irrespective of whether it is a part of primary key or not, the entire table is placed offline. Placed offline essentially means that one cant issue any select/insert/update/delete on the table and also all the other indexes associated with the table are disabled. When a clustered primary key is disabled, in addition to the impact happening because of disabling clustered index, the foreign key constraints on the tables that are referencing the disabled primary key are disabled as well.But the most important part is when you enable back your primary key, the foreign key constraints still remain disabled. We will see a quick example.

I have a student table with sid column as clustered primary key.


CREATE TABLE [dbo].[Student]
  (
     [sid]          [INT] NOT NULL,
     [student_name] [VARCHAR](50) NULL,
     CONSTRAINT [PK_Student] PRIMARY KEY CLUSTERED ( [sid] ASC )
  )
GO
CREATE INDEX ix_student_name
  ON [Student]([student_name])
GO 


Student_marks table references student table. Script for the same is provided below.

CREATE TABLE [dbo].[student_marks]
  (
     [sid]          [INT] NOT NULL,
     [marks]        [INT] NULL,
     [subject_name] [VARCHAR](50) COLLATE sql_latin1_general_cp1_ci_as NOT NULL,
     [subject_id]   [INT] NOT NULL,
  )
GO
ALTER TABLE [dbo].[student_marks] ADD CONSTRAINT [FK_student_marks_Student]
FOREIGN KEY([sid]) REFERENCES [dbo].[Student] ([sid])
GO 


Let us disable the primary key on the student table. It can be done by


ALTER INDEX pk_student ON dbo.student DISABLE
GO 


Warning: Foreign key 'FK_student_marks_Student' on table 'student_marks' referencing table 'Student' was disabled as a result of disabling the index 'PK_Student'.
Warning: Index 'IX_student_name' on table 'Student' was disabled as a result of disabling the clustered index on the table.


We obtain a warning message saying that the Non Clustered Index on student table and the foreign key constraint on student_marks table are disabled.The same can be confirmed by querying sys.foreign_keys tabled. The is_disabled column with a value one indicates that the constraint is disabled.


SELECT Object_name(parent_object_id)     AS table_name,
       Object_name(referenced_object_id) AS referenced_pky_tbl_name,
       is_disabled,
       name                              AS constraint_name,
       type_desc
FROM   sys.foreign_keys
WHERE  Object_name(referenced_object_id) LIKE 'student' 




Now let me enable the Primary key constraint back on student table.
The command used is provided below.


ALTER INDEX ALL ON dbo.student rebuild 


Primary key constraint is enabled by using the Index Rebuild command. One can rebuild just the Clustered index to enable the primary key but that would not enable the other non clustered indexes on the table. ALTER INDEX ALL enables all the indexes that are present in the table and hence it is the recommended command if you have disabled your clustered primary key.

To prove that foreign key is not enabled,let me insert a row into student_marks table which violates the constraint.I execute the following script which effectively proves the same.


SELECT *
FROM   student
WHERE  sid = -1

GO /*-- To ensure that no row on student has sid = -1 */
INSERT INTO student_marks
            (sid,
             subject_id,
             subject_name,
             marks)
SELECT -1,
       1,
       'Tamil',
       60
GO

SELECT *
FROM   student_marks
WHERE  sid = -1

GO /* to confirm that insert was successful */
SELECT Object_name(parent_object_id)     AS table_name,
       Object_name(referenced_object_id) AS referenced_pky_tbl_name,
       is_disabled,
       name                              AS constraint_name,
       type_desc
FROM   sys.foreign_keys
WHERE  Object_name(referenced_object_id) LIKE 'student'
GO 




To enable the constraint first delete the invalid row and then use Check constraint command.


DELETE student_marks
WHERE  sid = -1
GO 


ALTER TABLE [dbo].[student_marks] WITH CHECK CHECK CONSTRAINT CONSTRAINT [FK_student_marks_Student]

So, the conclusion of this post is always remember to enable the foreign key constraints after you have disabled and enabled your primary key. Let me finish the post with a quick script to generate a script for enabling foreign key
constraints.


DECLARE @primary_key_tbl_name VARCHAR(500)

SET @primary_key_tbl_name = 'student'

SELECT 'ALTER TABLE [' + sys.schemas.name + '].[' +
       Object_name(parent_object_id) +
              '] WITH CHECK CHECK CONSTRAINT [' + sys.foreign_keys.name + ']' AS
       enable_fky
FROM   sys.foreign_keys,
       sys.schemas
WHERE  Object_name(referenced_object_id) LIKE @primary_key_tbl_name
       AND is_disabled = 1
       AND TYPE = 'F'
       AND sys.schemas.schema_id = sys.foreign_keys.schema_id
GO 


Set the value of @primary_key_tbl_name to the table whose primary key you have disabled. Executed the script after you have disabled and enabled the primary key. The Script pulls the related foriegn keys to the table and generates the enable script for the same.