Integrity Constraint

<< CSQL Join - TOC - CSQL Indexes >>

This section talks about CSQL database constraints such as NOT NULL, DEFAULT, UNIQUE, PRIMARY KEY and FOREIGN KEY. CSQL and its associated suite of products have a single design objective and that is undisruptive performance, Because of this main goal it supports some of the important constraints (handles them in database level) and rest of the constraints when necessary are being handled at application level. The following are the important constraints supported by the CSQL database.

NOT NULL

A not-null constraint specifies that a column must have some value. For that a NULL value should not be applied for a NOT NULL field. Example: 1 

CSQL>CREATE TABLE products ( 
product_no INTEGER NOT NULL, 
name CHAR (20) NOT NULL, 
price FLOAT 
);

If NULL value is inserted in NOT NULL specified field it will raise NOT NULL constraint violation error.

Example: 2 

CSQL>insert into products values (1,NULL, 7000);

8160:3086649568:TableImpl.cxx:681:NOT NULL constraint violation for field name
8160:3086649568:TableImpl.cxx:443:Unable to copy values from bind buffer
Statement execute failed with error -22

NOT NULL constraint is a Domain-Integrity constraint. That means it can restrict a column from storing NULL value. A not-null constraint is always written as a column constraint.

UNIQUE

Unique constraints ensure that the data contained in a column or a group of columns is unique with respect to all the rows in the table. The UNIQUE constraint could be used when working with Index. After table creation UNIQUE key can be applied on specific field or group of fields for index creation.

Example: 1 Suppose the user want to create unique index on "roll" field of "stud" table, it can be created as follows: 

CSQL>CREATE INDEX idx1 ON stud (roll) UNIQUE;
Statement Executed

Index on multiple fields of an existing table can be created. This is called Composite Unique index. 

CSQL>create index idx2 on stud (roll, name) unique;
Statement Executed

In general, a unique constraint is violated when trying to insert a row with index field values that is already present in the table.

PRIMARY KEY Constraint

Relational database theory says that every entity of a relational schema must be uniquely identified from each other. A primary key indicates that a column or group of columns can be used as a unique identifier for rows in the table. As per the relational Database theory every table must have a primary key. This rule is not enforced by CSQL, but it is usually best to follow it. A table can have at most one primary key. Primary key is the combination of NOT NULL and UNIQUE constraints.

Example: 1 

CREATE TABLE emp(	
emp_no INT, 
	name CHAR(20) NOT NULL, 
	dept_no INT, PRIMARY KEY(emp_no));

Primary key also be applied by combining two or more fields. This is called Composite primary Key.

Example: 2 

create table emp(
ename char(20),
dept_no int,
job char (10),
age int,
primary key (ename, dept_no)
);

Here ‘emp’ table having four fields out of which ‘ename’ and ‘dept_no’ together behave as PRIMARY KEY.

FOREIGN KEY Constraint

Foreign Key constraint is a column (or a group of columns) whose values are derived from the primary key or unique key of some other table. Further, It is used to specify a foreign key for a referencing table, which means it is used to create relationship between tables

This constraint specifies a column or a list of columns as a foreign key of the referencing table. The referencing table is called the Foreign-key-Table, and the references table is called Primary-key-Table.

Example: 

CREATE TABLE EMPLOYEE (
empno int,…,
deptno int,…,
FOREIGN KEY(deptno) REFERENCES DEPT(deptno));

In the above example, Lets say we have a DEPT table having the primary key ‘deptno’ which is a UNIQUE and NOT NULL field. Here, we have the EMPLOYEE table, which is referencing the ‘deptno’ from the DEPT table. So the syntax we use is,

Syntax: 

CREATE TABLE  <FK_TAB_NAME> (
    Col_Name Col_datatype, …,
    FOREIGN KEY (Col_Name) REFERENCES                     
    <PK_TAB_NAME>(PKcol_Name),…  
);

It is ensure that when the data is entered for the ‘deptno’ column of EMPLOYEE table, It either can be null or it has to be a value present in the ‘deptno’ column of DEPT table. Note that Foreign Key Constraint defined at Table Level.

  • Different column of different Foreign-Key-Tables can references one column of a Primary-Key-Table.
  • More than one column of a Foreign-Key-Table can references to more than one Primary-Key-Table.

<< CSQL Join - TOC - CSQL Indexes >>

Page last modified on October 13, 2009, at 01:57 AM