Post reply

NULL and NOT NULL in Default Constraint

  • March 6, 2009 at 3:45 am

    #93283

    Hi Experts,

    What is the difference in having a column with NULL , Default constraint and NOT NULL, Default constraint.I tried creating both but not find any difference as with default value NULL is not accepted but still the column is having null.

    TIA

  • March 6, 2009 at 8:24 am

    #954925

    Try this:

    create table #test (

    testField int NOT NULL DEFAULT 0,

    testField2 int NULL DEFAULT 0

    )

    INSERT INTO #test DEFAULT VALUES

    INSERT INTO #test VALUES (1,NULL)

    You'll get the values:

    testField testField2

    0 0

    1 NULL

    Regards

    Gianluca

    -- Gianluca Sartori

  • March 10, 2009 at 2:30 pm

    #957087

    After running the script, try adding this line: insert into #test values(NULL,1); This one will fail because the first column does not allow nulls to be added (testField int NOT NULL DEFAULT 0).

    Constraints are used to limit the values that can be added to a column. NOT NULL is a form of this. Another example is to limit testField to values between 1 and 10. You cannot do this on temp table so use the following script:

    CREATE TABLE test (

    testField int NOT NULL DEFAULT 0,

    testField2 int NULL DEFAULT 0

    );

    GO

    ALTER TABLE test WITH CHECK ADD CONSTRAINT [CK_TestTable] CHECK (([testField]>=(1) AND [testField]<=(10)));

    GO

    INSERT INTO test(testField,testField2) VALUES(11,5);

    --The third statement will fail because the testField value is outside of the constraint range.

    Cheers,

    Brian

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply