SQL NOT NULL Constraint
The SQL NOT NULL constraint is used to restrict a column to NOT accept NULL values.
By default, a field can store NULL values. If you do not want a field to have a NULL value, then you need to define that column as NOT NULL constraint.
The SQL NOT NULL constraint enforces a column to always contain a value except NULL value. This means that you cannot insert a new row, or update a existing row without adding or setting a value to this column.
Related Links
SQL NOT NULL Constraint Example
The following SQL statement create the "BookID" field and the "BookName" field to not accept NULL values, in the "Books" table:
CREATE TABLE Books
(
BookID INT NOT NULL,
BookName VARCHAR(255) NOT NULL,
AuthorName VARCHAR(255),
BookPrice DECIMAL (8, 2)
);
In the above example, the following SQL statement creates a new table called Books and adds four fields.
Here, BookID and BookName fields has a SQL NOT NULL constraint to validate newly entered records.
After executing above query, the "Books" table look like this:
BookId | BookName | AuthorName | BookPrice | |
---|---|---|---|---|
To insert records on "Books" table, use the following SQL statements:
INSERT INTO Books (BookID, BookName, AuthorName, BookPrice)
VALUES (111, 'PL Sql Complete Programming','Suresh Babu', 160);
INSERT INTO Books (BookName, AuthorName, BookPrice)
VALUES ('Sql Database Hacking Program', 'Vinoth Kumar', 75);
INSERT INTO Books (BookID, AuthorName, BookPrice)
VALUES (333, 'Vinoth Kumar', 75);
INSERT INTO Books (BookID, BookName, AuthorName, BookPrice)
VALUES (444, 'Sql Database Performance', 'Bala Haris Varshith', 75);
INSERT INTO Books (BookID, BookName, BookPrice)
VALUES (555, 'Sql Database Analysis', 175);
The SQL statement above would insert three (but there are five records tried to insert) new records into the "Books" table.
The second record will not inserted into table. Bcoz the record doesn't provide a value for BookID column. The given value must NOT be a NULL value.
The third record also will not inserted into table. Bcoz the record doesn't provide a value for BookName column.
After executing above query, the "Books" table look like this:
BookId | BookName | AuthorName | BookPrice |
---|---|---|---|
111 | PL Sql Complete Programming | Suresh Babu | 160 |
444 | Sql Database Performance | Bala Haris Varshith | 180 |
555 | Sql Database Analysis | 175 |
Note: The last record hab been inserted into table. Bcoz the AuthorName column will support NULL value.
Related Links