Wednesday , November 13 2019
Home / SQL / Define FOREIGN KEY constraint by CREATE TABLE statement in Oracle

Define FOREIGN KEY constraint by CREATE TABLE statement in Oracle

You can define FOREIGN KEY constraint, when you create a table.

Practice:

This practice will focus how to define FOREIGN KEY constraint while you create a table by CREATE TABLE statement. In the practice, we will use two tables named REGISTRATION table as a parent and MARKS table as a child.

Example:    

Step-1:

Drop the child table MARKS first if already exists.

DROP TABLE Marks;

Then drop the parent table REGISTRATION.

DROP TABLE Registration;

Note: If you attempt to drop parent table keeping child table, then the system cannot drop the table and generate a system error which is the followings:

DROP TABLE Registration

Error at line 1

ORA-02449: unique/primary keys in table referenced by foreign keys

So, you have to drop child table first and then drop the parent table,

Step-2:

First create the parent table REGISTRATION containing 4 columns where PRIMARY KEY has been set on REGISTRATIONNO column.

CREATE TABLE Registration

(

RegistrationNo      Varchar2(10) CONSTRAINT pk_regid    PRIMARY KEY,

StudentName         Varchar2(100),

Subject                    Varchar2(50),

PhoneNo                 Varchar2(15)

);

And secondly create the child table MARKS where FOREIGN KEY has been set on REGISTRATIONNO column.

CREATE TABLE Marks

(

RegistrationNo     Varchar2(10) REFERENCES  Registration(RegistrationNo),

ExamName            Varchar2(50),

TotalMark              Number(4,2),

OutOf                      Number(1)

);

Note:

The FOREIGN KEY column name and length can be different from the PRIMARY KEY column of parent table. But datatype of FOREIGN KEY column must be same as PRIMARY KEY column of parent table.

If you attempt to use different datatype in FOREIGN KEY column, then the child table will not be created and generate a system error.

If you write the following code for MARKS table where the data type of REGISTRATIONNO column is declared NUMBER instead of VARCHAR2 which is the data type of parent table column REGISTRATIONNO like the following:

CREATE TABLE Marks

(

RegistrationNo     Number  REFERENCES  Registration(RegistrationNo),

ExamName            Varchar2(50),

TotalMark              Number(4,2),

OutOf                      Number(1)

);

If you execute the above code, then oracle will generate the following error due to incompatible data type with referenced (parent) table column. In result, the MARKS table will not be created.

CREATE TABLE Marks

(

RegistrationNo        Number   REFERENCES  Registration(RegistrationNo),

ExamName              Varchar2(50),

TotalMark                Number(4,2),

OutOf                        Number(1)

)

Error at line 1

ORA-02267: column type incompatible with referenced column type

So, the datatype of child table must be same as the datatype of parent table (referenced table).

Step-3:

Now insert three rows into the parent table REGISTRATION:

INSERT     INTO    Registration

(RegistrationNo, StudentName, Subject, PhoneNo)

VALUES (‘0521′,’Md. Abdul Gani’,’CSE’,’01711454648′);

INSERT    INTO    Registration

(RegistrationNo, StudentName, Subject, PhoneNo)

VALUES (‘0522′,’Md. Abul Hasan’,’CSA’,’01812546585′);

INSERT     INTO    Registration (RegistrationNo,StudentName,Subject,PhoneNo)

VALUES (‘0533′,’Md. Jabbar Ali’,’MCSE’,’01745632352′);

Now commit and query to view the data.

COMMIT;

SELECT *  FROM  Registration;

Step-4:

insert three rows into the parent table REGISTRATION:

Commit and query to confirm data.

COMMIT;

SELECT * FROM ProductInfo;

Step-5:

In this step, insert the same record that already inserted.

INSERT    INTO   ProductInfo (Product_Id, Product_Name, Product_Type,Unit)  VALUES (‘P-1001′,’SOAP’,’GENERAL’,’PCS’);

When you attempt to insert the record whose PRODUCT_ID is ‘P-1001’ already exists, the following error will be generated by oracle and the row will not be inserted for primary key constraint (PERVEJ.PK_PROD_ID) violation.

INSERT   INTO   ProductInfo (Product_Id, Product_Name, Product_Type,Unit) VALUES (‘P-1001′,’SOAP’,’GENERAL’,’PCS’)

Error at line 1

ORA-00001: unique constraint (PERVEJ.PK_PROD_ID) violated

In the circumstance, you have to insert the unique value of PRODUCT_ID column that has not been inserted yet.

Step-6:

Change the value of PRODUCT_ID to ‘P-1002’ instead of ‘P-1001’ and execute INSERT the statement.

INSERT    INTO   ProductInfo

(Product_Id, Product_Name, Product_Type,Unit)

VALUES (‘P-1002′,’SHAMPOO’,’GENERAL’,’PCS’);

Step-7:

Commit and query to confirm whether row has been inserted successfully.

COMMIT;

SELECT Product_Id, Product_Name, Product_Type,Unit FROM ProductInfo;

The Result set of SELECT statement:

PRODUCT_ID  PRODUCT_NAME PRODUCT_TYPE   UNIT

P-1001                SOAP                    GENERAL             PCS

P-1002               SHAMPOO           GENERAL             PCS

From the above result set, we see that the record for PRODUCT_ID P-1002 has been inserted successfully in the PRODUCTINFO table.

This is how; we can define FOREIGN KEY constraint in CREATE TABLE statement.

Check Also

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: …

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 have been absent for some time, but now I remember why I used to love this blog. Thanks, I?¦ll try and check back more frequently. How frequently you update your site?

Leave a Reply

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