INTRODUCTION to DDL
A database scheme is specified by a set of definition which are expressed by a special language called a Data Definition Language. The result of compilation of DDL statement is a set of tables which are stored in a special file called Data Dictionary.
-> DDL defines the structure of data.
-> DDL statements in SQL language are responsible for creating or modifying database structures such as tables, views, and indexes.
Let us now understand DDL statements and how it works on Oracle database.
TABLE(A table consist of Rows and Columns. Table is a collection of related records that are treated as a unit. )
Convention for Naming a Table in Oracle-> Each table owned by a Oracle account must have a unique name.
-> A table name cannot exceed 30 characters in length.
-> A table name must begin with an alphabetic character.
-> A table name can contain the letters A through Z, the digits 0 through 9, and the characters $, #, and _(underscore).
-> A table name cannot be an SQL reserved word.
-> You can use uppercase and lowercase characters in naming tables; oracle is not case sensitive as long as table or column names are not enclosed in double quotes.
-> A table name should be descriptive.
Convention for Naming Column in Oracle.-> Within a single table, a column name must be unique. However, you may use the same column name in different tables.
-> Like other database objects, a column name can be upto 30 characters long.
-> The column name must begin with an alphabetic character.
-> The column name can contain the letters A through Z, the digits 0 through 9, and the characters $, #, and _(underscore). The name cannot include spaces.
-> A column name cannot be an SQL reserved word.
-> As with tables, be descriptive in naming a column. Descriptive column names help users understand the definition of each column.
-> An Oracle table may have upto 254 columns.
Commands Related To Table
1) Syntax to CREATE TableCREATE TABLE
(
[column level constraint],
:
:
,
[Table level constraint]
);
Here,
-> Table_name is the name for the table.
-> Column_name1 through Column_nameN are valid column names.
-> Datatypes is a valid Oracle datatype specification.
-> Constraint_name is a optional, but it should be used in order to avoid invalid information to be entered in the database.
NOTE:-you can assign default value for field by using the keyword default and specifying default value for that field.
eg:-city varchar2(25) default 'AHMEDABAD'
(Though SQL is not case sensitive, but contents of table are case sensitive so it is good practise to write data always in uppercase).
Tip - CHAR, VARCHAR AND DATE kind of data should be written in single quota.
example
sql> create table emp
(
emp_no number (6) primary key,
emp_name varchar2(35),
emp_address varchar2(45),
emp_city varchar2(30),
emp_state varchar2(30),
emp_age number(2),
emp_sex char,
emp_department varchar2(30)
);
Table created.
2) Describe Table-> This command will describe the table.
Syntax for describing table
sql> desc;
or
sql> describe;
example
sql> desc emp;
3) ALTER Table-> Alter command is used to make modification after table has created.
-> Modification like to add column, to drop constraint like primary key,etc., However you cannot delete the column nor you can decrease the width of column.
Syntax to ALTER Table definition
ALTER TABLE
[ADD MODIFY DROP]
( ,
:
:
);
examples
-> To Add column in a table.
sql> alter table emp
add
(emp_phno number(10));
-> To Modify Table
sql> alter table emp
modify
(
emp_city varchar2(35),
emp_state varchar2(35)
);
-> To Drop Table's Primary key and other constraints.
general syntax
ALTER TABLE
[DROP ENABLE DISABLE]
CONSTRAINT;
sql> alter table emp
drop primary key;
After successfully completion of above command you get the message table altered.
4) Command to Delete contents of Table
TRUNCATE-> Truncate command will delete all the table contents by removing all the rows(records) from the table.
-> After the implementation of this command, only the structure of the table remains.
Syntax for Truncate command
sql> TRUNCATE TABLE;
example
sql> truncate table emp;
-> It will delete all the rows of the emp table and only structure of the table remains.
5) Command to Delete Table
DROP-> Drop command will delete the table contents and its structure.
Syntax for Drop command
sql> DROP TABLE;
example
sql> drop table emp;
-> It will delete all the rows and the structure of emp table.
6) Command to Rename Table-> Rename command will rename the table name.
Syntax for Rename command
Sql > RENAMETO
example
sql> Rename employee to emp;
-> Now the table name is renamed and you can manipulate it using name emp.
INTEGRITY CONSTRAINTSAn Integrity constraint is a trick used by oracle to prevent invalid data entry into the table. It is only a rules applied to restrict user to enter particular value. It prevent this by defining it at column-level or table-level constraint. The main difference between both is that column-level constraint is defined at the column level and cannot reference by any other columns in the table. A table-level constraint is defined at the table level and can reference to any of the table's columns.
NOTE:-not null and check constraint cannot be defined as table-level constraint.
Integrity constraint are categorized into three types they are as under
1) Domain Integrity Constraints
2) Entity Integrity Constraints
3) Referential Integrity Constraint.
I) DOMAIN INTEGRITY CONSTRAINTS-> Domain integrity constraint Prevent invalid data entry by enforcing rules like NOT NULL and CHECK on the column.
NOT NULL->By default, every column can contain null value. But, If we want to restrict the user to enter the value at any cost we should put not null constraint.
->In other words, not null field should always contains value.
example
create table emp
(
empno number(4) constraint ntnl not null,
ename varchar2(25),
job varchar2(25)
);
->here empno is column-level constraint and after implementation of the above command while user want to enter the value, he must have to enter empno. ntnl is the constraint name.
CHECK-> check constraint is used to match the enter data with predifined criteria during designing of table.
-> As it check for predifined criteria it stops the invalid user to do mischief with the database.
-> Thus it helps in making database consistent by feeding reliable information.
example
create table emp
(
empno number(4) constraint ntnl not null,
ename varchar2(25),
job varchar2(25) constraint check(job in('clerk','manager'))
);
->here check constraint will look for job in clerk or manager and if enduser try's to enter job for another category an error code for it will be generated by sql.
II) ENTITY INTEGRITY CONSTRAINTS-> Entity integrity constraint Prevent invalid data entry by enforcing rules like UNIQUE and PRIMARY KEY on the column.
UNIQUE-> Unique constraint allowed null values, but it does not allowed duplicate values.
-> It may be composite upto 16 columns.
example
create table emp
(
empno number(4),
ename varchar2(25),
job varchar2(25),
constraint unino unique(empno)
);
->here unino is constraint name for table-level constraint definition and constraint unique is applied to empno, so after execution of above command user can enter only unique value or may not enter the value.
PRIMARY KEY-> A primary key is a field that uniquely identifies each records in a table.
-> It should neither be duplicate nor null.
-> It may be composite upto 16 columns.
example
create table emp
(
empno number(4) constraint pkno primary key ,
ename varchar2(25),
job varchar2(25)
);
->here Primary key is created for empno so it will alone identifies each records in a table. pkno is a constraint name for column-level definition of constraint.
III) REFRENTIAL INTEGRITY CONSTRAINTS-> Often, we wish to ensure that a value that appers in one relation for a given set of attributes also appears for a certain set of attributes in another realtion. This condition is called referential integrity.
-> To implement this, we should define the column in the parent table as a primary key and the same column in the child table as a foreign key referring to the corresponding parent entry.
->foreign key is a column or combination of column which refers to primary key of primary table.
example
create table emp
(
empno number(4) constraint pkno primary key ,
ename varchar2(25),
job varchar2(25)
);
create table education_detail
(
empno number(4) ,
degree varchar2(30),
foreign key (empno) references emp(empno)
);
-> here emp table contains the details of the employee while education_detail table consist of their achived degree's by their no. one-to-many relationship is formed here. As one employee have many degree's and even few employee have no degree's.
ON CASCADE DELETE-> It is special facilty through which we can ensure that the row deleted in the master table will automatically deletes the row in the reference table.
for example if we have records of employee details than after deleting any one of their record will automatically deletes the corresponding entry in the reference table.
example
create table education_detail
(
empno number(4) references emp(empno) on-delete-cascade,
degree varchar2(30)
);
-> DDL defines the structure of data.
-> DDL statements in SQL language are responsible for creating or modifying database structures such as tables, views, and indexes.
Let us now understand DDL statements and how it works on Oracle database.
TABLE(A table consist of Rows and Columns. Table is a collection of related records that are treated as a unit. )
Convention for Naming a Table in Oracle-> Each table owned by a Oracle account must have a unique name.
-> A table name cannot exceed 30 characters in length.
-> A table name must begin with an alphabetic character.
-> A table name can contain the letters A through Z, the digits 0 through 9, and the characters $, #, and _(underscore).
-> A table name cannot be an SQL reserved word.
-> You can use uppercase and lowercase characters in naming tables; oracle is not case sensitive as long as table or column names are not enclosed in double quotes.
-> A table name should be descriptive.
Convention for Naming Column in Oracle.-> Within a single table, a column name must be unique. However, you may use the same column name in different tables.
-> Like other database objects, a column name can be upto 30 characters long.
-> The column name must begin with an alphabetic character.
-> The column name can contain the letters A through Z, the digits 0 through 9, and the characters $, #, and _(underscore). The name cannot include spaces.
-> A column name cannot be an SQL reserved word.
-> As with tables, be descriptive in naming a column. Descriptive column names help users understand the definition of each column.
-> An Oracle table may have upto 254 columns.
Commands Related To Table
1) Syntax to CREATE TableCREATE TABLE
(
:
:
[Table level constraint]
);
Here,
-> Table_name is the name for the table.
-> Column_name1 through Column_nameN are valid column names.
-> Datatypes is a valid Oracle datatype specification.
-> Constraint_name is a optional, but it should be used in order to avoid invalid information to be entered in the database.
NOTE:-you can assign default value for field by using the keyword default and specifying default value for that field.
eg:-city varchar2(25) default 'AHMEDABAD'
(Though SQL is not case sensitive, but contents of table are case sensitive so it is good practise to write data always in uppercase).
Tip - CHAR, VARCHAR AND DATE kind of data should be written in single quota.
example
sql> create table emp
(
emp_no number (6) primary key,
emp_name varchar2(35),
emp_address varchar2(45),
emp_city varchar2(30),
emp_state varchar2(30),
emp_age number(2),
emp_sex char,
emp_department varchar2(30)
);
Table created.
2) Describe Table-> This command will describe the table.
Syntax for describing table
sql> desc
or
sql> describe
example
sql> desc emp;
3) ALTER Table-> Alter command is used to make modification after table has created.
-> Modification like to add column, to drop constraint like primary key,etc., However you cannot delete the column nor you can decrease the width of column.
Syntax to ALTER Table definition
ALTER TABLE
[ADD MODIFY DROP]
(
:
:
examples
-> To Add column in a table.
sql> alter table emp
add
(emp_phno number(10));
-> To Modify Table
sql> alter table emp
modify
(
emp_city varchar2(35),
emp_state varchar2(35)
);
-> To Drop Table's Primary key and other constraints.
general syntax
ALTER TABLE
[DROP ENABLE DISABLE]
CONSTRAINT
sql> alter table emp
drop primary key;
After successfully completion of above command you get the message table altered.
4) Command to Delete contents of Table
TRUNCATE-> Truncate command will delete all the table contents by removing all the rows(records) from the table.
-> After the implementation of this command, only the structure of the table remains.
Syntax for Truncate command
sql> TRUNCATE TABLE
example
sql> truncate table emp;
-> It will delete all the rows of the emp table and only structure of the table remains.
5) Command to Delete Table
DROP-> Drop command will delete the table contents and its structure.
Syntax for Drop command
sql> DROP TABLE
example
sql> drop table emp;
-> It will delete all the rows and the structure of emp table.
6) Command to Rename Table-> Rename command will rename the table name.
Syntax for Rename command
Sql > RENAME
example
sql> Rename employee to emp;
-> Now the table name is renamed and you can manipulate it using name emp.
INTEGRITY CONSTRAINTSAn Integrity constraint is a trick used by oracle to prevent invalid data entry into the table. It is only a rules applied to restrict user to enter particular value. It prevent this by defining it at column-level or table-level constraint. The main difference between both is that column-level constraint is defined at the column level and cannot reference by any other columns in the table. A table-level constraint is defined at the table level and can reference to any of the table's columns.
NOTE:-not null and check constraint cannot be defined as table-level constraint.
Integrity constraint are categorized into three types they are as under
1) Domain Integrity Constraints
2) Entity Integrity Constraints
3) Referential Integrity Constraint.
I) DOMAIN INTEGRITY CONSTRAINTS-> Domain integrity constraint Prevent invalid data entry by enforcing rules like NOT NULL and CHECK on the column.
NOT NULL->By default, every column can contain null value. But, If we want to restrict the user to enter the value at any cost we should put not null constraint.
->In other words, not null field should always contains value.
example
create table emp
(
empno number(4) constraint ntnl not null,
ename varchar2(25),
job varchar2(25)
);
->here empno is column-level constraint and after implementation of the above command while user want to enter the value, he must have to enter empno. ntnl is the constraint name.
CHECK-> check constraint is used to match the enter data with predifined criteria during designing of table.
-> As it check for predifined criteria it stops the invalid user to do mischief with the database.
-> Thus it helps in making database consistent by feeding reliable information.
example
create table emp
(
empno number(4) constraint ntnl not null,
ename varchar2(25),
job varchar2(25) constraint check(job in('clerk','manager'))
);
->here check constraint will look for job in clerk or manager and if enduser try's to enter job for another category an error code for it will be generated by sql.
II) ENTITY INTEGRITY CONSTRAINTS-> Entity integrity constraint Prevent invalid data entry by enforcing rules like UNIQUE and PRIMARY KEY on the column.
UNIQUE-> Unique constraint allowed null values, but it does not allowed duplicate values.
-> It may be composite upto 16 columns.
example
create table emp
(
empno number(4),
ename varchar2(25),
job varchar2(25),
constraint unino unique(empno)
);
->here unino is constraint name for table-level constraint definition and constraint unique is applied to empno, so after execution of above command user can enter only unique value or may not enter the value.
PRIMARY KEY-> A primary key is a field that uniquely identifies each records in a table.
-> It should neither be duplicate nor null.
-> It may be composite upto 16 columns.
example
create table emp
(
empno number(4) constraint pkno primary key ,
ename varchar2(25),
job varchar2(25)
);
->here Primary key is created for empno so it will alone identifies each records in a table. pkno is a constraint name for column-level definition of constraint.
III) REFRENTIAL INTEGRITY CONSTRAINTS-> Often, we wish to ensure that a value that appers in one relation for a given set of attributes also appears for a certain set of attributes in another realtion. This condition is called referential integrity.
-> To implement this, we should define the column in the parent table as a primary key and the same column in the child table as a foreign key referring to the corresponding parent entry.
->foreign key is a column or combination of column which refers to primary key of primary table.
example
create table emp
(
empno number(4) constraint pkno primary key ,
ename varchar2(25),
job varchar2(25)
);
create table education_detail
(
empno number(4) ,
degree varchar2(30),
foreign key (empno) references emp(empno)
);
-> here emp table contains the details of the employee while education_detail table consist of their achived degree's by their no. one-to-many relationship is formed here. As one employee have many degree's and even few employee have no degree's.
ON CASCADE DELETE-> It is special facilty through which we can ensure that the row deleted in the master table will automatically deletes the row in the reference table.
for example if we have records of employee details than after deleting any one of their record will automatically deletes the corresponding entry in the reference table.
example
create table education_detail
(
empno number(4) references emp(empno) on-delete-cascade,
degree varchar2(30)
);
0 comments:
Post a Comment