SQL Script for Index and Key Constraints (PrimaryKey, Non Clustered Index, ForeignKey and ReferenceKey)

To Create PK (Primary Key)

IF NOT EXISTS (SELECT * from sys.indexes

WHERE object_id = OBJECT_ID('[dbo].[CustomerRecord]')

AND Name = ('PK_CustomerRecord'))BEGIN ALTER TABLE [dbo].[CustomerRecord]

ADD CONSTRAINT [PK_CustomerRecord] PRIMARY KEY CLUSTERED (CustomerKey)

WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,SORT_IN_TEMPDB = OFF,

IGNORE_DUP_KEY = OFF,ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];END;

To Create Composite Primary Key (For two and more columns)

IF NOT EXISTS (SELECT * from sys.indexes

WHERE object_id = OBJECT_ID('[dbo].[CustomerRecord]')

AND Name = ('PK_CustomerRecord'))BEGIN ALTER TABLE [dbo].[CustomerRecord]

ADD CONSTRAINT [PK_CustomerRecord] PRIMARY KEY CLUSTERED (CustomerKey, CustomerID)

WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,SORT_IN_TEMPDB = OFF,

IGNORE_DUP_KEY = OFF,ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];END;

To Drop PK (Primary Key)

IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID('[dbo].[CustomerRecord]')

AND name = 'PK_CustomerRecord') BEGIN ALTER TABLE [dbo].[CustomerRecord]

DROP CONSTRAINT [PK_CustomerRecord]; END;

To Create Non-Clustered Index

IF NOT EXISTS (SELECT * FROM sys.indexes

WHERE object_id = OBJECT_ID('[dbo].[CustomerRecord]')

AND name = 'PK_CustomerRecord_CustomerId')

BEGIN CREATE NONCLUSTERED INDEX [PK_CustomerRecord_CustomerId]

ON dbo.CustomerRecord ([CustomerId] ASC) WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]; END;

To Create Multiple/Composite Non-Clustered Index

IF NOT EXISTS (SELECT * FROM sys.indexes

WHERE object_id = OBJECT_ID('[dbo].[CustomerRecord]')

AND name = 'PK_CustomerRecord_CustomerId')

BEGIN CREATE NONCLUSTERED INDEX [PK_CustomerRecord_CustomerId]

ON dbo.CustomerRecord ([CustomerId] ASC, [Branchcode] ASC) WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]; END;

To Drop Non-Clustered Index

IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID('[dbo].[CustomerRecord]')

AND name = 'PK_CustomerRecord_CustomerId')

BEGIN DROP INDEX [PK_CustomerRecord_CustomerId] ON [dbo].[CustomerRecord]

WITH ( ONLINE = OFF ); END;


To Drop FK (Foreign Key)



IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID('[dbo].[FK_DimSourceCode_DimCompany]') AND parent_object_id = OBJECT_ID('[dbo].[DimSourceCode]'))
BEGIN ALTER TABLE [dbo].[DimSourceCode]  DROP CONSTRAINT [FK_DimSourceCode_DimCompany]; END;


To Create FK (Foreign Key)


IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID('[dbo].[FK_DimSourceCode_DimCompany]') AND parent_object_id = OBJECT_ID('[dbo].[DimSourceCode]'))
BEGIN ALTER TABLE [dbo].[DimSourceCode]  WITH  CHECK  ADD CONSTRAINT [FK_DimSourceCode_DimCompany] FOREIGN KEY ([CompanyId]) REFERENCES [dbo].[DimCompany] ([CompanyId]) ON UPDATE  NO ACTION  ON DELETE  NO ACTION ; END;


To Drop RefKey (ReferenceKey)


IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID('[dbo].[FK_ProgramSourceXref_DimSourceCode]') AND parent_object_id = OBJECT_ID('[dbo].[ProgramSourceXref]')) BEGIN ALTER TABLE [dbo].[ProgramSourceXref]  DROP CONSTRAINT [FK_ProgramSourceXref_DimSourceCode]; END; 


To Create RefKey (ReferenceKey)


IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID('[dbo].[FK_ProgramSourceXref_DimSourceCode]') AND parent_object_id = OBJECT_ID('[dbo].[ProgramSourceXref]')) BEGIN ALTER TABLE [dbo].[ProgramSourceXref]  WITH  CHECK  ADD CONSTRAINT [FK_ProgramSourceXref_DimSourceCode] FOREIGN KEY ([SourceCodeId]) REFERENCES [dbo].[DimSourceCode] ([SourceCodeId]) ON UPDATE  NO ACTION  ON DELETE  NO ACTION ; END;