SqlTutorial-1 : Lesson-2 : Class-3
Microsoft SQL Server Training:
Free Online-Learning Classes (OLC) for T-SQL
Integrity Constraints: Default Value and Not-Null Constraints


1. Default

The DEFAULT-verb, defaults the column value when its value missing. SQL implicitly inserts NULL-values as default-value. By using DEFAULT-verb you can customize default value of a column.
  • It Specifies default value for a column when it contain empty-value.
  • You cannot default multiple columns using single DEFULT constraint.
  • Implicit value is NULL for default. However, using DEFAULT constraint you can customize default value to business value;

Examplex

  1. Defaulting Column Values with current Date.

    CREATE TABLE Shippers (
        ShipperID int IDENTITY NOT NULL PRIMARY KEY,
        ShipperName varchar(30) NOT NULL,
        DateInSystem smalldatetime NOT NULL DEFAULT GETDATE ()
    );
    // OR //
    CREATE TABLE Shippers (
        ….
        DateInSystem smalldatetime NOT NULL ) ;
        ALTER TABLE Shipppers 
            ADD CONSTRAINT CN_Date DEFAULT GETDATE() FOR DateInSystem 
    );

  2. Default String

    ALTER TABLE Employee
        ADD CONSTRAINT DF_ID
               DEFAULT "NO Name" FOR name

  3. Inserting Default values to records.

    CREATE TABLE xyz (
       col1 int PRIMARY KEY IDENTITY(1, 1) NOT NULL,
       col2 int NOT NULL DEFAULT 999,
       col3 char(10) NOT NULL DEFAULT 'ABCDEFGHIJK'
     )
    -- Test: Inserting all default values.
    INSERT xyz DEFAULT VALUES    // 1 999 ABCDEFGHIJK
    INSERT xyz DEFAULT VALUES   // 2 999 ABCDEFGHIJK
     // OR //
    INSERT INTO xyz (col2) VALUES(100) // 5 100 ABCDEFGHIJK

  4. Inserting Random Numbers (-9999 and 9999) as Default values

    CREATE TABLE Abc (
       Column_name  int NOT NULL DEFAULT
          CASE WHEN CONVERT(int, RAND() * 10) % 2 = 1
               THEN (CONVERT(int, RAND() * 100000) % 10000 * -1 )
               ELSE CONVERT(int, RAND() * 100000) % 10000
        END
    );
    -- Testing:
    INSERT INTO abc DEFAULT VALUES  //  5415
    INSERT INTO abc DEFAULT VALUES  //-7651
    RAND(): 0-1 positive values, Therefore, for 4-digit numbers mutiply it with 10000 and for getting negative no. multiply it with -1.

  5. Inserting Random Characters from A – Z as Default values.

    CREATE TABLE kkk (
        Column_name char(15) NOT NULL
        CHAR( (CONVERT(int, RAND() * 100) % 26 ) + 65)
    )
    --Testing
    INSERT INTO kkk DEFAULT VALUES => T
    INSERT INTO kkk DEFAULT VALUES => P
     RAND()* 100 % 26     ==> 0-25 values.
     RAND()*100 % 25 + 65 ==>  65 – 90 ASCII of (A-Z)

  6. Deleting DEFAULT constraints.

    DROP DEFAULT

  7. Inserting GUID ( NewID() returns value) as DEFAULT for a column.

    CREATE TABLE MyTable(
        PK_ID uniqueidentifier NOT NULL PRIMARY KEY
             DEFAULT (NEWID ()), Name char (30) NOT NULL
    )
    -- Testing
    INSERT MyTable (Name) VALUES ('F')
    INSERT MyTable (Name) VALUES ('W')


2. NOT NULL

Enforces that a column cannot contain Null-values, you can use this constraint with combination with UNIQUE, DEFAULT, CHECK, FOREIGN-Key.
Null values are inserted either implicitly or explicitly. By default a column contains null-values as resolving to missing values, since as per RDBMS concern, a relation(table) cell cannot be empty.
  • NOT-Null prevents a column to accept Null value (either submitted implicitly or explicitly)
  • It is best practice that use NOT NULL + DEFAULT custom values, as it automatically inserts business values instead of NULL when value missing

Examples

  1. CREATing Table with NOT-NULL Constraint

    The following T-SQL script creates a table with enforcement of NOT-NULL : LName and FName columns
    CREATE TABLE PersonTable (
               ID int primary Key
               LName varchar NOT NULL,
               FName varchar NOT NULL,
               Address varchar,
               City varchar
    )

  2. Adding NOT-NULL Constraints to exiting table

    -- Assum following table is already exists without NOT-NULL Constraint CREATE TABLE
    PersonTable (
               ID int primary Key,
               LName varchar,
               FName varchar,
               Address varchar,
               City varchar
    )
    GO;

    -- Adding NOT-NULL Constraint to LName and FName column
    ALTER TABLE PersonTable
       ALTER COLUMN LNAME varchar CONSTRAINT C_LName_Not_Null NOT NULL,
             COLUMN FNAME varchar CONSTRAINT C_F_Name_Not_Null NOT NULL;
    GO

    NOT-NULL constraints added along with columns Data-Type; You cannot add NOT-NULL without knowing columns data type


  3. Droping NOT-NULL Constraint

    ALTER TABLE PersonTable  DROP C_LName_Not_Null;
    ALTER TABLE PersonTable  DROP  C_FName_Not_Null;
      
NULL itself a value, it is not empty-field.

1 comment: