RDBMS Keys & Constraints
https://www.computersprofessor.com/2016/04/rdbms-keys-constraints.html
There are two types of data constraints that can be
applied to data inserted into a oracle table. There are I/O constraint [input/output]
and business rule constraint.
I/O CONSTRAINTS
The input /output constraints are further divided into different
types of constraints such as,
Ø Primary key constant
Ø Foreign Key constraint
Ø Not Null
Ø unique key
Ø Null constraint
Ø
Check constraint
THE PRIMARY KEY CONSTRAINT
A
primary key is one or more columns in a
table used to uniquely identify each row in a table. It means, it is used to
uniquely identify each row in the table. None of the fields that are part of
the primary key can contain a null value. A table can have only one primary key
Therefore, Primary Key =
Unique + Not Null.
FEATURES
1)
It will not allow duplicate values
2)
It will not allow null values
3)
Only one primary key is allowed for each table
PRIMAREY
KEY CONSTRAINST DEFIEND AT COLUM LEVEL SYNTAX:
Eg: Create table student (‘stu-no’ varchar(10) primary
key, ‘fname’ varchar (25), DOB date());
THE FOREGIN KEY [SELF REFERENCE] CONSTRAINT:
The
Foreign key represent relationships
between tables. A foreign key is a column whose values are derived from
the primary key or unique key of same or other table. The table in which the
foreign key is defined is called foreign table or Parent table. It can be
defined in either a create table or an after table statement
FEATURES :
1.
Master table cannot be updated if child record exist
2.
Parent that is being referenced has to be unique or primary key.
3.
Parent table record can be delete provide no child record exit.
4. Child table may have be duplicates
and null values, but unless it is
specified.
FOREIGN KEY CONSTRAINT DEFIEND AT THE
COLUMN LEVEL:
Syntax: <column name ><data type> (<size>) reference <Table name> [column name ] [no delete
cascade]
The unique key constraint
The
unique column constraint permits multiple
entries of null into the column. These null values are clubbed at the top of the
column in the order in which they were entered into table
UNIQUE CONSTRAINS DEFIEND AT COLUMN
LEVEL
SYNTAX: <COLUMN NAME ><data types> [(size)] unique
Eg:
Create table must-MSR (CUSTNO VARCHAR2(10) UNIQUE, ‘fname’ varchar2(25), ‘ Lname’
varchar (25) ;
NOT NULL CONSTRAINT:
In
addition to primary key and foreign keys, oracle has not null as column constraint. The not null column constraint ensures
that a table column cannot be left empty. When a column is defined as not null,
then that column becomes a mandatory column. It implies that a value must be
entered into the column if the record is to be accepted for storage in the
table.
SYNTAX: <COLUMN, NAME> <DATA TYPE>(<SIZE>)NOT NULL
Create table (stu_cde number(4) primary key, Name varchar2(20)
Not Null, Addr varchar2(30));
THE CHECK CONSTRAINTS:
Business rules validations can be applied to a table
column by using check constraint. Check constraints must be specified as a
logically expression that evaluates either true or false
SYNTAX: <column name> <data type><(size)>check(<logical expression>)
Eg: Create table cust_mstr(‘cust no’ varchar2(10) check(cust_no
like”c%”), fname varchar2(25));
Eg:
Create table < table_name>
(Field_name1 field_type(width) Primary key,
Field_name2 Field-type (width) Not Null,
Field_name3 field-type (width) check (field-names
in (‘a’, ‘b’, ‘c’));