SQL NOT NULL Constraint Examples

SQL NOT NULL Constraint :

In this article i will give the basic idea about the SQL Constraints and how it is used in real life scenarios. In Previous article I have given the basic idea about the functions in SQL with real world scenarios.’SQL Constraints’ means rules and regulations to normalize the data. SQL constraints are used to specify rules for the data in a table.  If there is any violation between the constraint and the data action, the action is aborted by the constraint. Constraints can be specified when the table is created (inside the CREATE TABLE statement) or after the table is created (inside the ALTER TABLE statement).Constraints could be column level or table level. Column level constraints are applied only to one column where as table level constraints are applied to the whole table. In This article I will focus on Different real life scenarios and examples of SQL NOT NULL Constraint.

‘SQL Constraints’ means rules and regulations to normalize the data

Following is one line description for all SQL Constraints:

• NOT NULL – Indicates that a column cannot store NULL value
• UNIQUE – Ensures that each row for a column must have a unique value
• PRIMARY KEY – A combination of a NOT NULL and UNIQUE. Ensures that a column (or combination of two or more columns) have an unique identity which helps to find a particular record in a table more easily and quickly
• FOREIGN KEY – Ensure the referential integrity of the data in one table to match values in another table
• CHECK – Ensures that the value in a column meets a specific condition
• DEFAULT – Specifies a default value when specified none for this column
• INDEX: Use to create and retrieve data from the database very quickly.

1) SQL NOT NULL :

By default, a table column can hold NULL values. The NOT NULL constraint enforces a column to NOT accept NULL values.

The NOT NULL constraint enforces a field to always contain a value. This means that you cannot insert a new record, or update a record without adding a value to this field.

How to add SQL NOT NULL Constraint?

   Real life Example:

The following SQL enforces the “RollNo” Column to NOT accept NULL values:

CREATE TABLE Student

(

RollNo Number (10) NOT NULL,

FName Varchar2 (15),

LName Varchar2 (15),

Location Varchar2 (20)

);

Example:

INSERT INTO Student

VALUES (101, ‘Rahul’, ‘Gandhi’, ‘Ranchi’);

INSERT INTO Student1 (RollNo, FName, LName, Location)

Values (”,’Rahul’, ‘Gandhi’, ‘Ranchi’);

ERROR at line 2:

ORA-01400: cannot insert NULL into (“Rahul”.”STUDENT”.”ROLLNO”)

 

Note: NOT NULL Constraint can be defined only at a Column level.

To DROP NOT NULL Constraint:

To Drop a NOT NULL Constraint, use the following SQL:

Syntax:

ALTER TABLE <Table_Name>

DROP CONSTRAINT <Constraint_Name>;

To find System Constraint:

SELECT Table_Name, Constraint_Name FROM User_Constraints;

Example:

ALTER TABLE Student1

DROP Constraint SYS_C005021;

Hope user will like this article on SQL Not Null Constraint with different real life industry examples.If you like this article or if you want to give some suggestions regarding the article please comment in comment section.