EX.NO:1
DATA DEFINITION LANGUAGE, TABLE CREATION, CONSTRAINTS
AIM:
To execute the Data Definition Language
(DDL) commands and creating the table with constraints using RDBMS.
Data Definition
Language-Syntax:
CREATE
TABLE
CREATE
TABLE <table name>
{
fieldname-1
datatype,
fieldname-2
datatype,
…….
Fieldname-n
datatype
};
CREATE TABLE <table name> AS
(
SELECT(att-list)
FROM <existing table name>
);
ALTER
TABLE
ALTER TABLE
<table name> ADD/MODIFY
(
fieldname-1
datatype,
fieldname-2
datatype,
…..
fieldname-n
datatype
);
ALTER TABLE
DROP COLUMN column name;
DROP
TABLE
- All data and structure in the table is deleted
- Any pending transactions are committed
- All indexes are dropped
- We can not rollback the drop table statement.
DROP TABLE
<table name>;
DESCRIBING TABLE
Desc table
name
CHANGING NAME OF AN
OBJECT
To change
the name of a table, view, sequence, or synonym, execute the rename statement.
Rename old
name to new name
TRUNCATE
The
truncate table statement :
·
Removes all rows from a table
·
Release the storage space used by that table
·
We cannot rollback row removal when using
truncate.
TRUNCATE
TABLE <table name>;
SET UNUSED OPTION:
Used to
mark one or more columns as unused.
ALTER TABLE table name SET UNUSED(column name )
(or)
ALTER TABLE table name SET UNUNSED COLUMN column name
REFERENCING ANOTHER
USER TABLE
Table belonging to other users are not in the
user’s schema, we should use the owner’s name as prefix to those tables.
Select *
from user name .table name
QUERYING THE DATA
DICTIONARY
·
To see the names of tables owned by the user
Select table_name from
user_tables;
·
To view distinct object types owned by the user
Select Distinct object_type from
user_objects;
·
To view tables, views, synonyms and sequences
owned by the user;
Select * from user_catalog;
CONSTRAINTS:
To
implement Domain Integrity Constraints, Entity Integrity Constraints,
Referential Integrity Constraints
Integrity
Constraints are used to enforce rules that the columns in a table have to
conform with. It is a mechanism used by Oracle to prevent invalid data entry
into the table.
- Domain Integrity Constraints
- Not Null Constraint – The enforcement of Not Null Constraints in a table ensures that the table contain values
- Check Constraint – Allow only a particular range of values
- Entity Integrity Constraints
- Unique Constraints – The unique constraint designates a Column or a group of columns as unique key. This allows only unique value to be stored in the column. Rejects duplication.
- Primary Key Constraints – Primary key similar to unique key. avoids duplication , relation between two tables , does not allow not null values
- Referential Integrity Constraints
Enforces relationship between
tables. It designates a column or group of columns as a foreign key
Ø Foreign
Key – establish a relationship with the specified primary or unique key in
another table
Parent Table – Referenced key
Child table – Primary or unique
table
Sample Output
CONSTRAINTS
NOT NULL,
UNIQUE AND CHECK CONSTRINTS
SQL>
create table vendor_master(vencode varchar(5) unique,
venname varchar(7) not null);
Table
created.
SQL>
desc vendor_master;
Name Null? Type
-----------------------------------------
-------- ---------
VENCODE
VARCHAR2(5)
VENNAME NOT NULL
VARCHAR2(7)
SQL>
alter table vendor_master add(productprice number(10)
check(productprice<10000));
Table
altered.
SQL>
insert into vendor_master values('v001','Sony TV',9100);
1 row
created.
UNIQUE
CONSTRAINT VIOLATION
SQL>
insert into vendor_master values('&vencode','&venname',
&productprice);
Enter value
for vencode: v001
Enter value
for venname: Philips
Enter value
for productprice: 6000
old 1: insert into vendor_master
values('&vencode','&venname',
&productprice)
new 1: insert into vendor_master
values('v001','Philips',6000)
insert into
vendor_master values('v001','Philips',6000)
*
ERROR at
line 1:
ORA-00001:
unique constraint (USER06.SYS_C002807) violated
SQL> ed
Wrote file
afiedt.buf
1* insert into vendor_master
values('&vencode','&venname',
&productprice)
SQL> /
Enter value
for vencode: v002
Enter value
for venname: Sony TV
Enter value
for productprice: 6000
old 1: insert into vendor_master
values('&vencode','&venname',
&productprice)
new 1: insert into vendor_master
values('v002','Sony TV',6000)
1 row
created.
NULL
CONSTRAINT VIOLATION
SQL> /
Enter value
for vencode: v003
Enter value
for venname:
Enter value
for productprice: 9000
old 1: insert into vendor_master
values('&vencode','&venname',
&productprice)
new 1: insert into vendor_master
values('v003','',9000)
insert into
vendor_master values('v003','',9000)
*
ERROR at
line 1:
ORA-01400:
cannot insert NULL into ("USER06"."VENDOR_MASTER".
"VENNAME")
SQL> /
Enter value
for vencode: v003
Enter value
for venname: Philips
Enter value
for productprice: 8000
old 1: insert into vendor_master
values('&vencode','&venname',
&productprice)
new 1: insert into vendor_master
values('v003','Philips',8000)
1 row
created.
CHECK
CONSTRAINT VIOLATION
SQL> /
Enter value
for vencode: v004
Enter value
for venname: Akai
Enter value
for productprice: 12000
old 1: insert into vendor_master
values('&vencode','&venname',
&productprice)
new 1: insert into vendor_master
values('v004','Akai',12000)
insert into
vendor_master values('v004','Akai',12000)
*
ERROR at line
1:
ORA-02290:
check constraint (USER06.SYS_C002809) violated
SQL> /
Enter value
for vencode: v005
Enter value
for venname: Aiwa
Enter value
for productprice: 8500
old 1: insert into vendor_master
values('&vencode','&venname',
&productprice)
new 1: insert into vendor_master
values('v005','Aiwa',8500)
1 row
created.
PRIMARY AND
FOREIGN KEY CONSTRAINTS
SQL>
create table order_master(orderno varchar(5) constraint order_prim
primary
key,odate date,
vencode varchar(5),o_status char(1) not null,deldate date);
Table
created.
SQL>
desc order_master;
Name
Null? Type
-----------------------------------------
-------- ------------------
ORDERNO NOT NULL
VARCHAR2(5)
ODATE
DATE
VENCODE
VARCHAR2(5)
O_STATUS NOT NULL
CHAR(1)
DELDATE
DATE
SQL>
create table order_detail(orderno varchar2(5) ,itemcode varchar(3),
qtyorder
number(3),
foreign
key(orderno) references order_master on delete cascade on
update
cascade);
Table
created.
SQL>
desc order_detail;
Name Null? Type
-----------------------------------------
-------- -----------------
ORDERNO
VARCHAR2(5)
ITEMCODE
VARCHAR2(3)
QTYORDER
NUMBER(3)
SQL>
insert into order_master values('&oredrno','&odate','&vencode',
'&o_status','&deldate');
Enter value
for oredrno: o001
Enter value
for odate: 12-aug-2009
Enter value
for vencode: v001
Enter value
for o_status: p
Enter value
for deldate: 12-sep-2009
old 1: insert into order_master
values('&oredrno','&odate','&vencode',
'&o_status','&deldate')
new 1: insert into order_master
values('o001','12-aug-2009','v001','p',
'12-sep-2009')
1 row
created.
PRIMARY KEY
CONSTRAINT VIOLATION
SQL> /
Enter value
for oredrno: o001
Enter value
for odate: 11-nov-2008
Enter value
for vencode: v002
Enter value
for o_status: p
Enter value
for deldate: 14-feb-2010
old 1: insert into order_master
values('&oredrno','&odate','&vencode',
'&o_status','&deldate')
new 1: insert into order_master
values('o001','11-nov-2008','v002','p',
'14-feb-2010')
insert into
order_master values('o001','11-nov-2008','v002','p',
'14-feb-2010')
*
ERROR at
line 1:
ORA-00001:
unique constraint (USER06.ORDER_PRIM) violated
SQL> /
Enter value
for oredrno: o002
Enter value
for odate: 13-jan-2007
Enter value
for vencode: v002
Enter value
for o_status: p
Enter value
for deldate: 16-oct-2009
old 1: insert into order_master
values('&oredrno','&odate','&vencode',
'&o_status','&deldate')
new 1: insert into order_master
values('o002','13-jan-2007','v002','p',
'16-oct-2009')
1 row
created.
SQL>
select * from order_master;
ORDER
ODATE VENCO O DELDATE
-----
--------- ----- - ---------
o001 11-NOV-08 voo2 p 14-FEB-10
o002 13-JAN-07 v002 p 16-OCT-09
FOREIGN KEY
CONSTRAINT VIOLATION
SQL>
insert into order_detail values('&orderno','&itemcode',&qtyorder);
Enter value
for orderno: o003
Enter value
for itemcode: i01
Enter value
for qtyorder: 25
old 1: insert into order_detail
values('&orderno','&itemcode',&qtyorder)
new 1: insert into order_detail
values('o003','i01',25)
insert into
order_detail values('o003','i01',25)
*
ERROR at
line 1:
ORA-02291:
integrity constraint (USER06.SYS_C002814) violated - parent key
not
found
SQL> /
Enter value
for orderno: o001
Enter value
for itemcode: i01
Enter value
for qtyorder: 25
old 1: insert into order_detail
values('&orderno','&itemcode',&qtyorder)
new 1: insert into order_detail
values('o001','i01',25)
1 row
created.
SQL>
select * from order_detail;
ORDER
ITE QTYORDER
----- ---
----------
o001 i01
25
SQL>delete
from order_master where orderno='o001'
*
ERROR at
line 1:
ORA-02292:
integrity constraint (USER06.SYS_C002814) violated - child
record
found
USING on
delete cascade with foreign key constraint
SQL>
drop table order_master;
Table
dropped.
SQL>
drop table order_detail;
Table
dropped.
SQL>
create table order_master(orderno varchar(5) constraint order_prim
primary key, odate date,
vencode varchar(5),o_status char(1) not null,deldate date);
SQL>create
table order_detail(orderno varchar2(5) ,itemcode varchar(3),
qtyorder
number(3),
foreign key(orderno) references order_master
on delete cascade);
Table
created.
SQL>
insert into order_master values('&oredrno','&odate','&vencode',
'&o_status','&deldate');
Enter value
for oredrno: o001
Enter value
for odate: 12-jan-2007
Enter value
for vencode: voo1
Enter value
for o_status: p
Enter value
for deldate: 13-jun-2008
old 1: insert into order_master
values('&oredrno','&odate','&vencode',
'&o_status','&deldate')
new 1: insert into order_master values('o001','12-jan-2007','voo1','p',
'13-jun-2008')
1 row
created.
SQL> /
Enter value
for oredrno: o002
Enter value
for odate: 24-sep-2008
Enter value
for vencode: voo2
Enter value
for o_status: p
Enter value
for deldate: 16-jan-2010
old 1: insert into order_master
values('&oredrno','&odate','&vencode',
'&o_status','&deldate')
new 1: insert into order_master
values('o002','24-sep-2008','voo2','p',
'16-jan-2010')
1 row
created.
SQL>
select * from order_master;
ORDER
ODATE VENCO O DELDATE
-----
--------- ----- - ---------
o001 12-JAN-07 voo1 p 13-JUN-08
o002 24-SEP-08 voo2 p 16-JAN-10
SQL>
insert into order_detail values('&orderno','&itemcode',&qtyorder);
Enter value
for orderno: o001
Enter value
for itemcode: i01
Enter value
for qtyorder: 25
old 1: insert into order_detail
values('&orderno','&itemcode',&qtyorder)
new 1: insert into order_detail
values('o001','i01',25)
1 row
created.
SQL> /
Enter value
for orderno: o002
Enter value
for itemcode: i02
Enter value
for qtyorder: 50
old 1: insert into order_detail
values('&orderno','&itemcode',&qtyorder)
new 1: insert into order_detail
values('o002','i02',50)
1 row
created.
SQL>
select * from order_detail;
ORDER
ITE QTYORDER
----- ---
----------
o001 i01 25
o002 i02
50
SQL>
delete from order_master where orderno='o001';
1 row
deleted.
SQL>
select * from order_master;
ORDER
ODATE VENCO O DELDATE
-----
--------- ----- - ---------
o002 24-SEP-08 voo2 p 16-JAN-10
SQL>
select * from order_detail;
ORDER
ITE QTYORDER
----- ---
----------
o002 i02
50
[Note:
Deletion of a row in parent is reflected in child table also. ]
RESULT:
Thus the Data Definition
Language (DDL) and Data Control Language (DCL) commands in RDBMS were executed
and verified.
No comments:
Post a Comment