/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.Main
	DROP CONSTRAINT fk_Main_Lookup
GO
ALTER TABLE dbo.Lookup SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_Main
	(
	KeyCol int NOT NULL,
	LookupId int NOT NULL,
	Constrained int NULL,
	DataCol int NULL
	)  ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_Main SET (LOCK_ESCALATION = TABLE)
GO
IF EXISTS(SELECT * FROM dbo.Main)
	 EXEC('INSERT INTO dbo.Tmp_Main (KeyCol, LookupId, Constrained, DataCol)
		SELECT KeyCol, LookupId, Constrained, CONVERT(int, DataCol) FROM dbo.Main WITH (HOLDLOCK TABLOCKX)')
GO
ALTER TABLE dbo.Sub
	DROP CONSTRAINT fk_Sub_Main
GO
DROP TABLE dbo.Main
GO
EXECUTE sp_rename N'dbo.Tmp_Main', N'Main', 'OBJECT' 
GO
ALTER TABLE dbo.Main ADD CONSTRAINT
	pk_Main PRIMARY KEY CLUSTERED 
	(
	KeyCol
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
CREATE NONCLUSTERED INDEX LookupId_ix ON dbo.Main
	(
	LookupId
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE dbo.Main ADD CONSTRAINT
	ck_constrained CHECK (([Constrained]>(0)))
GO
ALTER TABLE dbo.Main ADD CONSTRAINT
	fk_Main_Lookup FOREIGN KEY
	(
	LookupId
	) REFERENCES dbo.Lookup
	(
	LookupId
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
CREATE TRIGGER Audit_Main ON dbo.Main FOR INSERT, UPDATE AS
   INSERT Audit(TableName, KeyValue1, OtherCols)
      SELECT 'Main', ltrim(str(KeyCol)), 
             (SELECT LookupId, UselessCol, Constrained, DataCol
              FOR    XML RAW('Data'))
      FROM   inserted
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.Sub ADD CONSTRAINT
	fk_Sub_Main FOREIGN KEY
	(
	KeyCol
	) REFERENCES dbo.Main
	(
	KeyCol
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.Sub SET (LOCK_ESCALATION = TABLE)
GO
COMMIT