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

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:

This practice will cover to set the NOT NULL constraint in existing table by ALTER TABLE statement.

To continue the practice, the steps should be followed.

Step-1:

First drop the REGISTRTION table if already created  by DROP TABLE statement.

DROP TABLE  Registration;

Step-2:

Now create the REGISTRTION table without NOT NULL constraint.

CREATE TABLE  Registration

(

Reg_Id                  varchar2(10),

Student_Name    varchar2(100),

Address                varchar2(150),

MobileNo            varchar2(15)

);

DESCRIBE Registration;

The result of DESCRIBE command:

 Name                                  Null?    Type

 ————————————–   ——– —————-

 REG_ID                                             VARCHAR2(10)

 STUDENT_NAME                            VARCHAR2(100)

 ADDRESS                                          VARCHAR2(150)

 MOBILENO                                       VARCHAR2(15)

From  the result of DESCRIBE command, we see that there is no value of Null? column heading for REG_ID column. So, the REG_ID column in REGISTRATION table has been created without NOT NULL constraint that means the REG_ID column will allow null values.

Step-3:

If now we want to set NOT NULL constraint in the REG_ID column of existing REGISTRATION table which already created, we need to execute the ALTER TABLE statement.

ALTER TABLE Registration MODIFY Reg_ID  NOT NULL;

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 the value of Null? column heading for REG_ID column is NOT NULL. The REG_ID column in REGISTRATION table has been altered by NOT NULL constraint. So, now  the REG_ID column will not allow null values that means users are bound to provide a value for REG_ID column.

Step-4:

Now insert rows to confirm whether rows are inserting successfully.

Without REG_ID column value:

INSERT   INTO   Registration (Student_Name, Address, MobileNo)  VALUES  (‘MD. ABDUR RAHMAN’,’MYMENSINGH’,’01742445456′);

The INSERT statement will generate the following  error due to not providing NOT NULL column value of REG_ID.

INSERT  INTO  Registration (Student_Name, Address, MobileNo)  VALUES (‘MD. ABDUR RAHMAN’,’MYMENSINGH’,’01742445456′)

Error at line 1

ORA-01400: cannot insert NULL into (“ADMIN”.”REGISTRATION”.”REG_ID”)

With REG_ID column value:

Now run the corrected INSERT statement  below:

INSERT  INTO  Registration  (Reg_Id, Student_Name, Address, MobileNo)   VALUES  (‘0151254′,’MD. ABDUR RAHMAN’,’MYMENSINGH’,’01752418856′);

The above corrected INSERT statement will execute and insert row successfully. Because we have provided here the value of REG_ID column which is NOT NULL column.

Step-5:

In last, commit the changes  and query to see inserted rows.

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 after table creation.

Exercise:

1) Create a table named INVOICE  containing four columns are Invoice_No, Invoice_Date, Customer_Id, Customer_Name.

Now, set NOT NULL constraint in the following  columns –

     a) Invoice_No

     b) Customer_Id

2) Create STORAGE table by executing  the above statement

CREATE  TABLE   Storage

(

Store_No              varchar2(10),

Product_Name    varchar2(30),

Quantity              number,

Store_Date          date

);

Run the DESCRIBE command to check the STORAGE table structure.

DESCRIBE   Storage;

Now, set the NOT NULL constraint by ALTER TABLE statement on the column, Store_No.

3) How will you add NOT NULL constraint on the existing column of a table?

4) Describe the steps to set NOT NULL constraint on a column after creating a table.

5) Which statement is responsible for setting NOT NULL constraint on a existing table column?

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 CREATE TABLE statement in Oracle

Definition of NOT NULL constraint: The NOT NULL constraint is used to restrict null values …

One comment

  1. I just could not depart your site prior to suggesting that I actually enjoyed the usual information a person provide in your visitors? Is gonna be again incessantly in order to check out new posts.

Leave a Reply

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