Integrity Constraints in Relational Algebra

Integrity constraints are used to ensure accuracy and consistency of data in a relational database. Data integrity is handled in a relational database through the concept of referential integrity. Many types of integrity constraints play a role in referential integrity (RI). Integrity Constraints are used to apply business rules for the database tables. The Constraints can be defined in two ways :

1. The constraints can be specified immediately after the column definition. This is called column-level definition.
2. The constraints can be specified after all the columns are defined. This is called table-level definition.


1. Primary Key constraints:   

Primary key is the term used to identify one or more columns in a table that make a row of data unique. Although the primary key typically consists of one column in a table. The Primary Key constraints defines a column or combination of columns which uniquely identifies each row in the table. Syntax for define a Primary key at column level:
  column name datatype PRIMARY KEY  
example :

CREATE TABLE NAME(
 u_id INTEGER(4) NOT NULL    PRIMARY KEY,
 name    VARCHAR(20) NOT NULL,
);

Syntax for define a Primary key at table level:
 PRIMARY KEY (column_name1,column_name2,..) 
example : 

CREATE TABLE NAME(
 u_id INTEGER(4) NOT NULL PRIMARY KEY,
 name    VARCHAR(20) NOT NULL,
PRIMARY KEY (u_id));



2. Unique Constraints :

Unique key constraints ensures that a column or a group of columns in each row have a distinct value. A column(s) can have a null value but the values cannot be duplicated. Syntax for define a Unique key at column level:
 column name datatype UNIQUE
example :


CREATE TABLE NAME(
 u_id    INTEGER(4) NOT NULL  PRIMARY KEY,
 name    VARCHAR(20)     NOT NULL,
 phone   INTEGER(10) NULL         UNIQUE,
);

Syntax for define a Unique key at table level:
 UNIQUE(column_name)
example :

CREATE TABLE NAME(
 u_id    INTEGER(4) NOT NULL  PRIMARY KEY,
 name    VARCHAR(20)     NOT NULL,
 phone   INTEGER(10) NULL,
UNIQUE(phone));


3. Foreign Key Constraints

A foreign key is a column in a child table that references a primary key in the parent table. The Foreign Key Constraint identifies any column referencing the PRIMARY KEY in another table. It establishes a relationship between two columns in the same table or between different tables. For a column to be defined as a Foreign Key, it should be a defined as a Primary Key in the table which it is referring. One or more columns can be defined as Foreign key. Syntax for define a Foreign key at column level:
 REFERENCES Referenced_Table_name(column_name)
exmaple :

CREATE TABLE NAME(
 u_id        INTEGER(4) NOT NULL  PRIMARY KEY,
 name     VARCHAR(20)     NOT NULL,
 phone    INTEGER(10) NULL  UNIQUE,
 bank_name       VARCHAR(20) NOT NULL FOREIGN KEY REFERENCES account(bank_name),
);

Syntax for define a Foreign key at table level:
 FOREIGN KEY(column_name) REFERENCES referenced_table_name(column_name);
example :

CREATE TABLE NAME(
 u_id        INTEGER(4) NOT NULL  PRIMARY KEY,
 name     VARCHAR(20)     NOT NULL,
 phone    INTEGER(10) NULL  UNIQUE,
 bank_name       VARCHAR(20) NOT NULL,
FOREIGN KEY (bank_name) REFERENCES account(bank_name));

4. Check Constraints

The Check constraints can be utilized to check the validity of data entered into particular table columns. Check constraints are used to provide back-end database edits. General edits restrict values that can be entered into columns or objects, whether within the database itself. The check constraint is a way of providing another protective layer for the data. Check constraint can be applied for a single column or a group of columns. Syntax for define a Check constraint:
 CHECK (condition)
example :

CREATE TABLE NAME(
 u_id        INTEGER(4) NOT NULL  PRIMARY KEY,
 name     VARCHAR(20)     NOT NULL,
 gender  CHAR(1)  NOT NULL CHECK(gender in('M', 'F')),
 phone    INTEGER(10) NULL  UNIQUE,
 bank_name       VARCHAR(20) NOT NULL FOREIGN KEY REFERENCES account(bank_name),
);

5. NOT NULL Constraints:

The NOT NULL is a constraint that you can place on a table's column. This constraint disallows the entrance of NULL values into a column; in other words, data is required in a NOT NULL column for each row of data in the table. NULL is generally the default for a column if NOT NULL is not specified, allowing NULL values in a column. Previous examples use the keywords NULL and NOT NULL listed on the same line as each column and after the data type. 

Next Topic : -  

No comments:

Post a Comment