How to Add and Drop Foreign Key and other Constraint in Oracle 10g XE

Foreign key is a non-key attribute which depends on a Primary key column of another table.

Here in below given example in table EMPL, DEPTID is foreign key column and there is another table named DEPTL which is Primary key table and DEPTID

is Primary Key Column.

If Foreign Key is present in same Primary Key table then it called Self Referential Integrity.

1: Adding Constraint After table created

ON DELETE SET NULL:sets child value null
ON DELETE CASCADE: Child row will delete

eg.

 ALTER TABLE EMPL ADD CONSTRAINT FOREIGN KEY EMPL_DEPTID_FK FOREIGN KEY(DEPTID) REFRENCES DEPTL(DEPTID) ON DELETE SET NULL 

 ALTER TABLE EMPL ADD CONSTRAINT FOREIGN KEY EMPL_DEPTID_FK FOREIGN KEY(DEPTID) REFRENCES DEPTL(DEPTID) ON DELETE CASCADE  

2: Dropping Constraint

ALTER TABLE TABLENAME CONSTRAINT CONSTRAINT-NAME

eg.

 ALTER TABLE EMPL DROP CONSTRAINT EMPL_DEPTID_FK  

How to use Foreign Key and other Constraint in Oracle 10g XE

Foreign key is a non-key attribute which depends on a Primary key column of another table.

Here in below given example in table EMPL, DEPTID is foreign key column and there is another table named DEPTL which is Primary key table and DEPTID

is Primary Key Column.

If Foreign Key is present in same Primary Key table then it called Self Referential Integrity.

Primary Key and Unique Key both uniquely define a tuple(Row).

but the difference is that Primary key can't take null values and Unique Key can take Multiple null values.

Step 1: Create a Primary Key table DEPTL with DEPTID as Primary Key Column.

 CREATE TABLE DEPTL(DEPTID NUMBER(4) PRIMARY KEY,DEPTNAME VARCHAR2(20))  

Step 2: Create a table EMPL

 CREATE TABLE EMPL(ID NUMBER(20) NOT NULL,EMAIL VARCHAR2(20) CONSTRAINT EMPL_EMAIL_UK UNIQUE,  
 SALARY NUMBER(8,2) CHECK(sALARY>1000),DEPTID NUMBER(4),  
 CONSTRAINT EMPL_ID_PK PRIMARY KEY(ID),  
 CONSTRAINT EMPL_DEPT_FK FOREIGN KEY(DEPTID)  
 REFRENCES DEPTL(DEPTID))  

NOTE: NOT NULL Constraint can't be apply as a Table Level Constraint.

Foreign Key constraints using Visual Studio with SQL Server Database

Step 1:
File ->New->Website
Step 2:
In Solution Explorer Right click over App_data folder and click on add item and add Sql Server Database.

Step 3:

In Server Explorer Expand Database by clicking on + sign before database.mdf then right click over Tables and click on Add New Table.

Step 4:

Enter Column Name(eg. Id) and Data Type.(eg. int).

Then right click over Id and select Primary Key(So that this table will become primary key table)

Save this table by pressing ctrl+s

After Saving this table Sever Explorer will looks like as below given Image when you expand Table Folder by clicking + sign

Step 5:

Right click over table1 and click on Show data and enter data as shown below:

Step 6:

Create table2 by same above Procedure.(Note: column in table2 you are going to set as foreign key must have same data type(eg. int) as in primay key table1)

press OK to save table2. and close it.

After Saving this table Sever Explorer will looks like as below given Image when you expand Table Folder by clicking + sign

Step 7:

Before entering data in table2 double clicck on Table2 in Server Explorer.

Then click on Relationships button 

you can click on it by different ways shown below

This button also appears over Server Explorer.

This option you can also get by right click over Id column in Table2.

Then after click Foreign Key Relationships window will appear. and click on Add button.

Select Tables And Columns Specifications

(click on this)

Tables and Columns window will appear. Set all values as shown.

press OK.
after that screen will looks like...

close this window by clicking close button.

IMP: press ctrl+s and save dbo.Table2 first then do the data Entry in Table 2.
If you do entry in Table2 without saving it. then it will not follow Foreign Key constraints.
and when you try to save dbo.Table2 after entry in Table2 it will show ERROR given below:
{
'Table1' table saved successfully
'Table2' table
- Unable to create relationship 'FK_Table2_Table1'.  
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_Table2_Table1". The conflict occurred in database "~\FOREIGNKEY\APP_DATA\DATABASE.MDF", table "dbo.Table1", column 'Id'.
}
save dbo.Table2

Step 8: 
After saving dbo.Table2 make entry in Table2.

This is Foreign Key constraints due to Id in 2nd row not matched with the entry in primary key Table1.