Monday , September 23 2019
Home / SQL / Set NOT NULL constraint by CREATE TABLE statement in Oracle

Set NOT NULL constraint by CREATE TABLE statement in Oracle

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.

Syntax:

CREATE    TABLE    table_name

(

column_name      datatype(length)   NOT NULL;

);

Here,

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.

Restrictions:

  • 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.

Practice:

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.

Step-1:

Drop the REGISTRTION table if already exists.

DROP TABLE  Registration;

Step-2:

Create the REGISTRTION table by four columns.

CREATE TABLE  Registration

(

Reg_Id                  varchar2(10)   NOT NULL,

Student_Name    varchar2(100),

Address                 varchar2(150),

MobileNo             varchar2(15)

);

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.

DESCRIBE Registration;

The result of DESCRIBE command:

Name                                    Null?             Type

 —————————–         ——–    ————–

 REG_ID                              NOT NULL     VARCHAR2(10)

 STUDENT_NAME                                      VARCHAR2(100)

 ADDRESS                                                    VARCHAR2(150)

 MOBILENO                                                VARCHAR2(15)

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.

Step-5:

Now commit the change and query the REGISTRATION to confirm whether the row has been inserted or not.

COMMIT;

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.

Check Also

Unique Constraint

Definition and Set UNIQUE constraint by CREATE TABLE statement in Oracle

Definition of UNIQUE constraint: The UNIQUE constraint is used to ensure unique or distinct values …

Set NOT NULL constraint by ALTER TABLE statement in Oracle

You can set NOT NULL constraint on existing table column by ALTER TABLE statement. Practice: …

One comment

  1. With havin so much content do you ever run into any issues of plagorism or copyright violation? My blog has a lot of exclusive content I’ve either authored myself or outsourced but it seems a lot of it is popping it up all over the internet without my authorization. Do you know any techniques to help prevent content from being ripped off? I’d genuinely appreciate it.

Leave a Reply

Your email address will not be published. Required fields are marked *