Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2011
    Posts
    47

    Unanswered: how to force not null constraint on columns that have datetime & int as there datatyp

    how to force not null constraint on columns that have datetime & int as there datatype ?
    please show with example

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Code:
    CREATE TABLE DaTable(
      Column1   INT          NOT NULL,
      Column2   DATETIME     NOT NULL
    )
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Sep 2011
    Posts
    71

    Lightbulb Alter exist table

    Hello ,
    As I understood you, you want update table ,Which already exist ,So
    try this code

    Code:
    ALTER TABLE dbo.MyTable ALTER COLUMN Created DATETIME NOT NULL DEFAULT  GETDATE();
    NOTE THAT GETDATE() TO USE CURRENT DATE ,YOU CAN USE WHATEVER

  4. #4
    Join Date
    Sep 2011
    Posts
    75
    Hello,

    Please uncheck the option of allow nulls while adding those columns

  5. #5
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Quote Originally Posted by paultech View Post
    Hello ,
    As I understood you, you want update table ,Which already exist
    After reading Paul's comment, I realise I may have misinterpreted your question. Do you want to
    - create a new table with NOT NULL constraints on columns that have datetime & int as their data types
    Code:
    CREATE TABLE DaTable(
      Column1   INT          NOT NULL,
      Column2   DATETIME     NOT NULL
    )
    - In an existing table add extra columns that have datetime & int as their data type, with a NOT NULL constraint.
    You will be adding new columns to an existing number of records. The values of those new columns will be NULL, but the constraint doesn't allow any NULL values. Solution: declare a default value for the new columns.
    Code:
    ALTER TABLE #DaTable
      ADD	Column1   INT	    NOT NULL	DEFAULT 0,
    	Column2   DATETIME  NOT NULL	DEFAULT GETDATE()
    ;
    - In an existing table with existing datetime & int columns, add a NOT NULL constraint on those columns.
    First give a value to all the nullable columns you want to make mandatory (NO NULL). Then add the NOT NULL constraint
    Code:
    UPDATE #DaTable
    SET Column1 = COALESCE(Column1, 0)
    WHERE Column1 IS NULL
    
    UPDATE #DaTable
    SET Column12 = COALESCE(Column1, GetDate)
    WHERE Column2 IS NULL
    
    ALTER TABLE #DaTable
      ALTER COLUMN Column1   INT	NOT NULL	
    
    ALTER TABLE #DaTable
      ALTER COLUMN Column2   DATETIME	NOT NULL
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •