Mouli K Mouli K - 2 months ago 8
SQL Question

How to add NOT NULL constraint where the column data type is NON INTEGER (say VARCHAR(50))

-- Trying to add NOT NULL constraint to an existing table.
-- Got succeeded for the first column (EMPID)
-- but failed to add for second column (EMPNAME). quiery as bellow.

CREATE TABLE tbl_primarykeyPOST2
( EMPID INT
,EMPNAME VARCHAR(50)
)
INSERT INTO tbl_primarykeyPOST2

SELECT '1','GURMIT SINGH'union all
select'2','KISHORE KUMAR 'union all
select'3','SHAHID ANWAR'union all
select'4','ANDREW'union all
select'5','ISMAIL'
SELECT * FROM tbl_primarykeyPOST2

ALTER TABLE tbl_primarykeyPOST2
ALTER COLUMN EMPNAME VARCHAR NOT NULL

Answer

When specifying the type VARCHAR() in SQL Server, you always need to specify the length. This is a good idea, even where there is a default length (the default actually varies by context). So, try this:

ALTER TABLE tbl_primarykeyPOST2 
    ALTER COLUMN EMPNAME VARCHAR(50) NOT NULL ;