LAB 7

Objective:- To Implement the restrictions on the table.

Data constraints:

Besides the cell name, cell length and cell data type there are other

parameters i.e. other data constrains that can be passed to the DBA at check creation

time. The constraints can either be placed at column level or at the table level.

i. Column Level Constraints:

If the constraints are defined along with the column

definition, it is called a column level constraint.

ii. Table Level Constraints:

If the data constraint attached to a specify cell in a

table reference the contents of another cell in the table then the user will have to

use table level constraints.

Null Value Concepts:-

while creating tables if a row locks a data value for particular column that value is said to be null . Column of any data types may contain null values unless the column was defined as not null when the table was created

Syntax:

Create table tablename

(

columnname data type (size) not null ……)

Primary Key:

primary key is one or more columns is a table used to uniquely

identity each row in the table. Primary key values must not be null and must be

unique across the column. A multicolumn primary key is called composite

primary key.

Syntax: primary key as a column constraint

Create table tablename

(columnname datatype (size) primary key,….)

Primary key as a table constraint

Create table tablename

(columnname datatype (size), columnname datatype( size)…

Primary key (columnname,columnname));

Default value concept: At

the line of cell creation a default value can be assigned

to it. When the user is loading a record with values and leaves this cell empty, the

DBA will automatically load this cell with the default value specified. The data

type of the default value should match the data type of the column

Syntax:

Create table tablename

(columnname datatype (size) default value,….);

Foreign Key Concept :

Foreign key represents relationship between tables. A

foreign key is column whose values are derived from the primary key of the same

of some other table . the existence of foreign key implies that the table with

foreign key is related to the primary key table from which the foreign key is

derived .A foreign key must have corresponding primary key value in the primary

key table to have meaning.

Foreign key as a column constraint

Syntax :

Create table table name

(columnname datatype (size) references another table name);

Foreign key as a table constraint:

Syntax :

Create table name

(columnname datatype (size)….

primary key (columnname);

foreign key (columnname)references table name);

Check Integrity Constraints:

Use the check constraints when you need to

enforce integrity rules that can be evaluated based on a logical expression

following are a few examples of appropriate check constraints.

!

A check constraints name column of the client_master so that the name is

entered in upper case.

!

A check constraint on the client_no column of the client _master so that

no client_no value starts with ‘c’

Syntax:

Create table tablename

(columnname datatype (size) CONSTRAINT constraintname)

Check (expression));

Question.2 Create the following tables:

i. Sales_master

Columnname Datatype Size Attributes

Salesman_no varchar2 6 Primary key/first letter

must start with ‘s’

Sal_name varchar2 20 Not null

Address varchar2 Not null

City varchar2 20

State varchar2 20

Pincode Number 6

Sal_amt Number 8,2 Not null, cannot be 0

Tgt_to_get Number 6,2 Not null, cannot be 0

Ytd_sales Number 6,2 Not null, cannot be 0

Remarks Varchar2 30