Foreign Key Constraint Creation Part 6 - Difference between Oracle and MS SQL Server

MSSQL Scripts

 

CREATE TABLE Department(

            DepartmentID varchar(12) NOT NULL primary key,

            DepartemntName varchar(100) NULL,

            ParentDepartmentID varchar(12) NULL,

            ModifiedDate datetime NULL);

 

Create table Emp(

EmpID varchar(10) not null primary key,

Firstname varchar(50) not null,

Lastname varchar(50) not null,

DepartmentID varchar(12) Foreign key references Department(DepartmentID));

 

To Check Table CONSTRAINTS

 

SELECT CONSTRAINT_NAME

FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS

WHERE TABLE_NAME = 'Emp'

 

ALTER TABLE emp DROP CONSTRAINT FK__Emp__DepartmentI__7E37BEF6

 

ALTER TABLE emp ADD CONSTRAINT emp_deptid Foreign key (DepartmentID) references Department(departmentid);

ALTER TABLE emp DROP CONSTRAINT emp_deptid

 

ORACLE Scripts

 

CREATE TABLE Department(

            DepartmentID varchar2(12) NOT NULL primary key,

            DepartemntName varchar2(100) NULL,

            ParentDepartmentID varchar2(12) NULL,

            ModifiedDate date NULL);

 

 

 

 

 

 

 

 

 

Create table emp(

EmpID varchar2(10) not null Primary key,

FirstName varchar2(50) not null,

LastName Varchar2(50) Not null,

DepartmentID varchar(12) Foreign key references Department(DepartmentID));

 

To Check Table CONSTRAINTS

 

SELECT *

FROM user_constraints

WHERE table_name = 'EMP'

 

Alter table EMP drop constraint SYS_C0011752

Alter table EMP drop constraint Emp_DeptID

 

Alter table EMP add constraint Emp_DeptID Foreign key (DepartmentID) references Department(Departmentid)

 

insert into Department values ('101','Sales',null,getdate())

insert into emp values ('201','Ravi','Kumar','100')

insert into emp values ('202','Suresh','Kumar','101')