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.