Add new Column with Foreign key Constraint

ADDING NEW COLUMN AFTER TABLE CREATION  with FK Constraint

 alter table emp
 add deptno number(3,0)
 add constraint fk_deptno
 foreign key (deptno) references dept(deptno)
on delete cascade

after above option  table  EMP will be  look like

• Here DEPTNO is Foreign key of DEPT table.

1. ON DELETE CASCADE option is  optionally.  this  option will  delete all the record when user delete any deptno from its parent table DEPT.
2. dept is Parent table.
3. all Child record  will be deleted when its releted Parent table is deleted.

Create table with Column level constraint for Dept table.

table name ::  DEPT
--------------------------------------------
Create table Command for DEPT table
create table dept
(deptno number(3,0) primary key,
dname varchar2(15) not null,
location varchar2(15)
)

table description after above Command is as under.

Here we Give column level Constraint for DEPTNO.

Here DEPT NO is  Primary key for  Dept table.

create Table in oracle with simple primary key and check constraint.

Table name :Emp
-------------------

Create table CODE:
create table emp
(
empid numeric(3,0),
ename varchar2(15) not null,
egender char(1),
esal number(5,0),
constraint chk_Pk_empid primary key(empid),
constraint chk_gender check(egender in('m','f','M','F')),
constraint chk_sal check(esal between 0 and 9999)
)

After table Create code table definition will be look like.

1. Here Empid is Primary key of Emp table.
2. Ename should be not null entry.
3. gender data should be 'M','F','m','f'.
4. salary should be in range of  0-9999 range. 

Here we  give constraint after column created. this constraint are know as Table level constraint.
here the benefit of column level constraint we can refer multiple column at the same time.
in Column level constraint we can't refer multiple column we only can refer current column.

we see that Constraint Topic in coming posts.