Post reply

NOT NULL CONSTRAINT

  • December 22, 2003 at 2:16 pm

    #106668

    How do you add a NOT NULL CONSTRAINT after the column was created

    ALTER TABLE dbo.STATES ADD CONSTRAINT NN_STATES_CountryID NOT NULL FOR CountryID

  • December 22, 2003 at 2:26 pm

    #487513

    If You don't have other constraints on the column like a default, etc

    then

    

    ALTER TABLE dbo.STATES 

    ALTER COLUMN CountryID YOUR_TYPE NOT NULL

    if you have other constraints,

    1. Drop them

    2. Perform like above

    3. Recreate Prev Constraints


    * Noel

  • December 22, 2003 at 5:31 pm

    #487533

    Thanks - That gets the "NOT NULL" job done but is there any way to name the CONSTRAINT as I try to get in the habbit of naming every contstraint I create.

    Thanks again

  • December 22, 2003 at 7:49 pm

    #487538

    That's a very good point. You should add constraints AFTER the table definion so that those can be named by yourself!

    if you plan to rename the constraint you will have to DROP it first an then RECREATED like:

    ALTER TABLE

    DROP CONSTRAINT OLD_NAME

    GO

    ALTER TABLE

    ADD CONSTRAINT NEW_NAME ....

    GO


    * Noel

  • December 23, 2003 at 5:35 am

    #487570

    Thanks - But if I can't figure out how to name the CONSTRAINT in the first place, I won't be able to rename it either. If I list my constraints by name, I don't see any of the "NOT NULL" consraints I have created, so SQL must not really treat them as a constraint.

    &nbsp&nbspSELECT 'Constraint' = O1.Name

    &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp, 'Table'=O.Name

    &nbsp&nbsp&nbsp&nbspFROM sysobjects&nbsp&nbsp&nbsp&nbsp&nbsp&nbspO LEFT JOIN

    &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp sysobjects&nbsp&nbsp&nbsp&nbsp O1&nbsp&nbsp ON O1.Parent_obj = O.id

    &nbsp&nbsp&nbspWHERE O1.Name IS NOT NULL

    I could also a CHECK CONSTRAINT

    ALTER TABLE dbo.STATES ADD CONSTRAINT CK_STATES_CountryID CHECK(CountryID IS NOT NULL)

    My understandign is that the CHECK CONSTRAINT is a wee bit less efficient than the native NOT NULL and being a purist I want the most efficient method possible.

    I figured if you can create a NOT NULL CONSTRAINT and Name the CONSTRAINT at CREATE TABLE time we should be able to do it a ALTER TABLE time

    Thanks again for you help, I really appreciate it.

Viewing 5 posts - 1 through 5 (of 5 total)

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