'Not Null Constraint' are used on a column to ensure that the specified column cannot have null values.
Not Null Constraint
The 'Not Null Constraint' enforces the rule that this column cannot remain null and must have a
value entered into it when a record is being inserted or updated.
SQL 'Not Null Constraint' Syntax
CREATE Table tablename
(
column1 datatype Primary Key,
column2 datatype Not Null,
column3 datatype,
column4 datatype,
.
.
.
columnn datatype
)
The above method of creating a table creates a 'Not Null' constraint on column2 at the column level.
The Not Null Constraint Example
CREATE TABLE Employees
(
EmployeeID smallint NOT NULL,
EmployeeName nvarchar(50) NOT NULL,
DateOfBirth smalldatetime NOT NULL,
DesignationID smallint NULL,
DeptID smallint NULL,
PhoneNo nvarchar(12) NULL,
City nvarchar(50) NOT NULL,
Salary decimal(5, 2) NULL
)
Note: When creating the columns, the data type of the column must be given so that you know what datatype it holds.
If it's a non integer, you need to specify the length of the column also. For a complete list of datatypes
of SQL Server 2008, see our
section link.
Observe that the columns with constraint NOT NULL has to be provided with data while inserting or updating the table.