DDL COMMANDS AND KEY CONSTRAINTS

DDL COMMANDS AND KEY CONSTRAINTS

AIM

To execute DDL commands andkey constraints using oracle.

syntax of the DDL commands;

1.To create a table.

create table <table name>(<col1><datatype>(size),<col2><datatype>(size));

2.modify the table.

i)Alter table <table name>add ()<new col><datatype>(size), <new col> datatype(size));

ii)Alter table <table name>modify(<col><new datatype>(new size));

3.Dropping a column from table.

i)Alter table<table name>drop column<col>;

ii)drop table<table name>;

4.Renameing the table.

Rename <old table>to<new table>;

5.Truncating the table.

Truncate table<table name>;

 Example of DDL commands;

SQL>create table emp(empname char(20),empid number(4),desn varchar(10),salary number(5),doj date);

     Table created.

SQL>alter table emp add(age number(2));

   Table altered.

SQL>alter table emp modify(empname varchar(25) );

   Table altered.

SQL>rename emp to employee;

   Table renamed.

SQL>alter table emp drop column desn;

   Table altered.

SQL>truncate  table employee;

   Table truncated.

SQL>drop table employee;

   Table droped.

syntax of the type of constraints;

(a).Not null constraint at column level

<col> <datatype> (size) not null;

(b).unique  constraint at column level

<col> <datatype> (size) unique;

(c).unique  constraint at table level

create table  <table name> (col=format,col=format unique(<col1>,<col2>));

(d)primary key constraint at column level

<col> <datatype> (size) primary key;

(e)primary key constraint at table level

create table <table name>(col=format,col=format primary key(<col1>,<col2>));

(f)foreign key constraint at column level

<col> <datatype> (size)  reference<table name>(<col1>);

(g)foreign key constraint at table level

foreign key (<col1>,<col2>) reference <table name>(<col1>,<col2>);

(h)check constraint at column level

<col> <datatype> (size) check(<logical expression>);

(i)check constraint at table level

 check(<logical expression>);

 Example of constraints

SQL>create table pdept(dno number(5),dname char(20),loc varchar(20),primary key(dno));

     Table created.

SQL>insert into pdept values(134,'IT','sss');

     1 row inserted.

SQL>insert into pdept values(111,'CEVIL','rrr');

     1 row inserted.

SQL>alter table pdept modify(loc varchar2(20)not null);

    Table altered.

SQL>drop table pdept;

SQL>create table pdept(dno number(5),dname char(10),loc varchar2(20));

     Table created.

SQL>alter table pdept add primary key(dno));

    Table altered.

SQL>create table fdept(did number(5)reference pdept(dno),ename varchar2(20),salary number(7));

   Table created.

SQL>insert into fdept values(202,'ECE',100);

   1 row inserted.

SQL>insert into fdept values(134,'IT',1000);

   1 row inserted.

SQL>create table customer(cnum number(5)not null,state varchar2(20)default(‘TN’),constraint cnum-pkkey primary key(cnum);

    Table created.

SQL> insert into customer(cnum)values(11);

     1 row inserted.

SQL> create table emp1(eid number(5)unique,ename varchar(20)default(‘unknow’),age number(4)not null,esal number(7) check(esal>10000));

    Table created.

SQL>insert into emp1(eid,age,esal)values(1,23,15000);

   1 row inserted.

SQL>insert into emp1(eid,age,esal)values(2,13,5000);

   1 row inserted.

                     

RESULT

                  Thus the DDL commands and key constraints were executed  successfully.