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.


Search Keys

  • not null sql query
  • alter table add not null
  • unique not null
  • oracle add constraint not null
  • alter table add constraint not null oracle
  • sql query where not null
  • sql in not null
  • select not null sql server
  • sql date not null
  • sql case not null
  • sql not null statement
  • sql isnull
  • ifnull sql
  • sql insert null value
  • sql insert null
  • 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.


Search Keys

  • null field
  • value null
  • select where null
  • sql update set null
  • sql where isnull
  • alter table nullable
  • set null sql
  • alter column null
  • nullable column
  • 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