I/O Constraints

Constrains that control data insertion and data retrieval speed are known as I/O (Input-Output) constraints.

  • It includes the following constraints.
  1. Primary Key
  2. Foreign Key
  3. Unique Key
  4. Not Null

Primary Key

  • Primary key is used to identify a record uniquely from the database table.
  • A primary key means UNIQUE + NOT NULL.
  • Value must be available for the column on which primary key has been defined.
  • User cannot leave the field blank.
  • It cannot contain duplicate values.
  • Primary key can be defined either at table level or at column level.
  • Primary key keyword is used to define primary key constraint

Primary key (at column level) :

CREATE TABLE student (  roll_no number (3) primary key,
                        name varchar2 (15),
                        city varchar2 (20) 
                      );

 Primary key (at table level):

CREATE TABLE emp ( first_name varchar2 (15),
                   last_name  varchar2 (15),
                   salary number (7, 2),
                   dept varchr2 (10), 
                   primary key (first_name, last_name)
                 );
  • We can define single primary key for a single table.
  • When a single column is not sufficient for the primary key purpose than primary key can be defined on multiple columns. Such primary key is known as composite primary key.
  • While defining primary key at column level “primary key” word is placed with the specified column on which primary key is to be defined.
  • While defining primary key at table level, primary key definition is placed after the table definition.
  • It is important to note that a single table can contain only one primary key but it may be define on multiple columns.
  • When primary key is defined then the behavior of the transaction will be as follow:
  1. It does not allow duplicate value for the primary key column.
  2. It does not allow null value that means we cannot left the field blank.
  3. A value must be present and it must be unique.

Foreign Key

  • A foreign key constraint is used to establish logical relationship between two or more tables.
  • Foreign key is also known as referential key.
  • It can be defined using the keyword “references”.
  • Normally we can establish relationship between two or more tables by using some common fields.
  • Generally a primary key is considered for defining relationship with other tables.
  • The main table which is logically linked with other table is known as ‘Parent table’ or “master table” while other table is referred to as ‘Child table’ or “detail table”.
  • The following point should be kept in the mind while defining foreign key.
  1. Data type and size of the parent table and child table should be the same.
  2. Record can be inserted in detail table only if relevant record is available into the master table that means while inserting record first we need to insert record into the parent table then and then we can insert record into the child table.
  3. To delete a specific record, first we need to delete a relevant record from the detail table and after that record can be deleted from the parent table.

 Foreign Key ( Column level )

    // Parent table definition

 CREATE TABLE student ( roll_no number (3) primary key,
                          name varchar2 (15),
                          city varchar2 (15)
                      );

// child table definition

CREATE TABLE stud_detail ( roll_no number (3) references student (roll_no),
                           address varchar2 (30),
                           city varchar2 (15)
                         );

Foreign Key ( Table level)

CREATE TABLE stud_detail ( roll_no number (3),
                           address varchar2 (30),
                           city varchar2 (15),
                           foreign key student (roll_no)
                         );

Unique Key

  • A unique key constraint can be defined when we do not want the user to enter duplicate values.
  • A unique constraint allows only unique value to be inserted.
  • However it allows null value to be inserted.
  • If the value is present for the unique constraint field then it must be unique.
  • Similar to not null constraint unique key can also be define on multiple columns.
  • “unique” keyword is use to define unique constraint.

Unique Key ( Column level )

CREATE TABLE student ( roll_no number (3),
                       name varchar2 (3),
                       mobile varchar2 (10) unique
                     );

Unique Key ( Table level )

CREATE TABLE student ( roll_no varchar2 (3),
                       name varchar2 (15),
                       city varchar2 (15), 
                       unique (roll_no, name)
                     );

Not Null

  • We can define not null constraint when we do not want the user to leave the field blank.
  • When any column contains not null constraint, the user must have to insert value for the column and it cannot be left blank.
  • However it allows duplicate value.
  • A table can contain not null constraint on multiple columns.
  • Not null constraint can only be defined at column level that means it cannot be defined at table level.
  • ‘Not Null’ keyword is used to define not null constraint.

Example:

CREATE TABLE student ( roll_no number (3) Primary key,
                       name varchar2 (15) not null,
                       mob number (15) not null
                     );