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:
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.
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.
Introduction Cryptocurrencies took the world by storm, setting up a new financial system and breaking…
In my previous article I have given Top 20 technical support interview questions with its…
In my previous articles I have given 15 most asked desktop support interview questions with…
A business analyst is someone who is versed in processes of data analysis used for…
In my previous article I have already given top questions and answers for Desktop support…
In my previous article I have given unix production support interview questions and answers. In…