Definition of NOT NULL constraint:
The NOT NULL constraint is used to restrict null values to be inserted in table column. By default, oracle allows null values to be inserted in column of a table. But if you want to prohibit null values, you have to set NOT NULL constraint in the table column.
Note: NOT NULL column will not allow null values in the column.
Note: NULL value means unknown data.
CREATE TABLE table_name
column_name datatype(length) NOT NULL;
NOT NULL keyword is used to define NOT NULL constraint in CREATE TABLE statement.
N.B: You can set NOT NULL constraint in two ways either CREATE TABLE statement or ALTER TABLE statement.
Note: If you do not define NULL or NOT NULL constraint on a column, the column will be defined as NULL able column by default. So, the column can contain null values until you are defining NOT NULL constraint on the null able column.
- You can’t define NULL or NOT NULL constraint in a view of the oracle database.
- An attribute of an Object cannot be specified as NULL or NOT NULL. You can use CHECK constraint with IS NULL or IS NOT NULL condition instead of specifying NULL or NOT NULL constraint for that attribute.
Set NOT NULL constraint by CREATE TABLE statement:
You can define NOT NULL constraint, when you create table by CREATE TABLE statement.
This practice will cover to set the NOT NULL constraint during table creation by CREATE TABLE statement.
To practice on NOT NULL constraint, the following steps should be run.
Drop the REGISTRTION table if already exists.
DROP TABLE Registration;
Create the REGISTRTION table by four columns.
CREATE TABLE Registration
Reg_Id varchar2(10) NOT NULL,
Here, REG_ID column has been set by NOT NULL constraint. So, REG_ID column will not allow null value. You must enter a value in REG_ID column. If you do not enter value, then oracle will generate error. NOT NULL keyword should be specified after column name and data type for which you want to set the constraint during table creation by CREATE TABLE statement..
Run the DESCRIBE command to see the table stucture.
The result of DESCRIBE command:
Name Null? Type
—————————– ——– ————–
REG_ID NOT NULL VARCHAR2(10)
From the result of DESCRIBE command, we see that REG_ID column has been set by NOT NULL.
Step-3: Without REG_ID column value:
Now try to insert a row without entering value for REG_ID column.
INSERT INTO Registration (Student_Name, Address, MobileNo) VALUES (‘MD. ABDUR RAZZAK’,’DHAKA’,’01712417456′);
Here, we are trying to insert values into STUDENT_NAME, ADDRESS and MOBILENO column of REGISTRATION table. But we have not provided any value for REG_ID column which is NOT NULL column. So, when we execute the statement, oracle will generate the following error.
INSERT INTO Registration (Student_Name, Address, MobileNo) VALUES (‘MD. ABDUR RAZZAK’,’DHAKA’,’01712417456′)
Error at line 1
ORA-01400: cannot insert NULL into (“ADMIN”.”REGISTRATION”.”REG_ID”)
Step-4: With REG_ID column value:
In step 3, correct the above INSERT statement by providing the value of REG_ID column and run it.
INSERT INTO Registration (Reg_Id, Student_Name, Address, MobileNo) VALUES (‘0101213′,’MD. ABDUR RAZZAK’,’DHAKA’,’01712417456′);
In the statement, we have provided value for REG_ID column. So it will execute and inert value successfully.
Now commit the change and query the REGISTRATION to confirm whether the row has been inserted or not.
SELECT * FROM Registration;
We see that the row with the value of REG_ID column has been inserted successfully.
This is how; we can set NOT NULL constraint during table creation.