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