Search code examples
sqlconstraintsnotnull

Why NOT NULL constraint can be added only in COLUMN LEVEL and ALTER LEVEL why can't we add in TABLE LEVEL?

I tried using alter command and it will be like ALTER TABLE MODIFY NOT NULL; and its working SO why cann't we add in table level like (CREATE TABLE (COL1 DATATYPE(SIZE),COL2 DATATYPE(SIZE), CONSTRAINT TN_COL1_NN NOT NULL(COL1));?

Solution

  • What i meant to say is for example primary key constraint can be added in three levels (COLUMN,TABLE AND ALTER).similarly we can add ALL rest of the constraints in three level why can't we add NOT NULL?

    I think what you are asking is how you can specify NULL or NOT NULL constraint on a column when you modify or create a table schema. The answer is you can. 

    -- when you define/create the table
CREATE TABLE MyTable (Name VARCHAR(100) NOT NULL)

-- when you add a new column to an existing table
ALTER MyTable ADD Title VARCHAR(100) NOT NULL

-- when you alter an existing column on an existing table
ALTER MyTable ALTER COLUMN Title VARCHAR(100) NOT NULL

    If you want to allow nulls in any of the above remove the word NOT.