RDBMS Keys & Constraints

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:

<Colum name> <data type> (size) primary key

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’));

Related

Oracle 8099284575625795085

Post a Comment

emo-but-icon

item