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.

You can also search these topics using not null sql query, alter table add not null, oracle add constraint not null, alter table add constraint not null oracle, sql query where not null, select not null sql server, sql date not null, sql case not null, sql not null statement, sql insert null value, sql server if null, sql server null value.

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.

You can also search these topics using select where null, sql update set null, alter table nullable, alter column null, sql server add not null constraint, sql not null default, oracle add not null column, alter table column not null oracle, constraint not null oracle, not null sql server, sql server not null, alter table column not null.