Click here to Skip to main content
15,879,474 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
SQL
IF NOT EXISTS(SELECT * FROM sys.objects WHERE type = 'PK' AND  parent_object_id = OBJECT_ID ('IN_LOOKUP'))
BEGIN 

	IF EXISTS (SELECT 1 FROM IN_LOOKUP WHERE LID IS NULL )	
		DELETE FROM IN_LOOKUP WHERE LID IS NULL
	
	print cast(@@ROWCOUNT  as varchar)+' '+  'Null records deleted..'

	IF EXISTS(SELECT 1 FROM (SELECT LID,COUNT(1) DUP FROM IN_LOOKUP	GROUP BY LID HAVING COUNT(1)>1) B)
	BEGIN
		WITH  DeleteDup 
		 AS
		(
		SELECT ROW_NUMBER() over(Partition by lid order by LID ) DUP ,* FROM IN_LOOKUP (NOLOCK)
		)

		DELETE from DeleteDup WHERE DUP>1
	END

	print cast(@@ROWCOUNT  as varchar)+' '+  'Dupilcate records deleted..'
	
	IF EXISTS (SELECT COL.IS_NULLABLE FROM INFORMATION_SCHEMA.COLUMNS COL WHERE COL.TABLE_NAME = 'IN_LOOKUP' and  COL.COLUMN_NAME='LID' AND COL.IS_NULLABLE ='YES')
			ALTER TABLE IN_LOOKUP ALTER COLUMN LID INT NOT NULL		
	
	IF NOT EXISTS(SELECT 1 FROM sys.objects	WHERE type = 'PK' AND  parent_object_id = OBJECT_ID ('IN_LOOKUP'))
			 ALTER TABLE IN_LOOKUP ADD CONSTRAINT PK_IN_LOOKUP PRIMARY KEY (LID)
		 
	PRINT 'Primary Key added'
END


Getting Errors Like:
Msg 8111, Level 16, State 1, Line 28
Cannot define PRIMARY KEY constraint on nullable column in table 'IN_LOOKUP'.
Msg 1750, Level 16, State 0, Line 28
Could not create constraint or index. See previous errors.


What I have tried:

I tried to solve but the cursor directly coming to IF NOT Exist statement in side the IF block.
Can any one help me out of this.
Posted
Updated 14-Dec-20 20:13pm
v3

1 solution

An error message is quite clear. You need to change column to not accept null values:
SQL
ALTER TABLE IN_LOOKUP ALTER COLUMN LID int NOT NULL

Then you'll be able to execute below lines:
SQL
IF NOT EXISTS(SELECT 1 FROM sys.objects	WHERE type = 'PK' AND  parent_object_id = OBJECT_ID ('IN_LOOKUP'))
			 ALTER TABLE IN_LOOKUP ADD CONSTRAINT PK_IN_LOOKUP PRIMARY KEY (LID)
		 
	PRINT 'Primary Key added'
 
Share this answer
 
Comments
Sandeep Mewara 15-Dec-20 2:24am    
5!
Maciej Los 15-Dec-20 3:50am    
Thank you, Sandeep.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900