Constrictions in Structured Query Language

The data types are a method for limiting the kind of information which can be kept in a table. For numerous applications, the constriction which is provided is too granular. For an instance, a column or attribute which stores product price must be accepting only positive information. However there is no data type which allows only positive numbers. An additional problem is that an individual may want to limit a particular column or attribute information with respect to some other columns or attributes or rows or tuples. For an instance, in a table or relation which has product data, there must be only one (1) row or tuple for every single product number.

To that end, Structured Query Language (SQL) permits an individual to describe constrictions on columns attributes and tables or relations. Constrictions give an individual as much governing control over the information in an individual tables or relation as he or she wishes. If a database user tries to stock information in a column or attribute which would disturb a constriction, then an error is shown. This applies even if the data came from the default data demarcation as well.

Check Constrictions

The check constriction is the most general constriction form. It permits an individual to stipulate that the data in a definite column or attribute should placate an indiscriminate expression. For an instance, to make it mandatory that a savings bank account outstanding balance should be positive only, then an individual can make use of the below mentioned syntax:

CREATE TABLE SavingAc ( Ac_ID INTEGER , Name CHAR , Balance INT CHECK ( Balance > 0 ) ) ;

As it can be seen, that the constriction demarcation is mentioned after the data type, just like default value demarcations. Default values as well as constrictions can be itemized in any sequence. A check constriction is comprised of the key word CHECK followed by an expression in parentheses. The check constriction expression must include the column or attribute therefore constrained, or else the constriction won’t make too much sense.

Not Null Constrictions

The not null constriction just stipulates that a column or attribute should not accept the NULL data. An example code is given as below:

CREATE TABLE SavingAc ( Ac_ID INTEGER NOT NULL , Name CHAR NOT NULL , Balance INT ) ;

A not null constriction at all times is written as a column or attributes constriction. A not null constriction is functionally equal to forming a check constriction CHECK ( My_Column_Name IS NOT NULL ), on the other hand in PostgreSQL forming an explicit not null constriction is more effectual. The disadvantage is that an individual cannot offer explicit names to not null constraints created that way.

Unique Constrictions

Unique constrictions guarantee that the information enclosed in a column or attribute otherwise in a group of columns or attributes is exclusive with respect to every the rows or tuples in the table or relations. The syntax of unique constriction when need to apply as a column or attribute constriction is given below:

CREATE TABLE SavingAc ( Ac_ID INTEGER UNIQUE , Name CHAR , Balance INT ) ;

The syntax of unique constriction when need to apply as a table or relation constriction is given below:

CREATE TABLE SavingAc ( Ac_ID INTEGER , Name CHAR , Balance INT , UNIQUE ( Ac_ID ) ) ;

Primary Key Constrictions

Theoretically, a primary key (PK) constriction is merely a grouping of a unique constriction and a not null constriction. Therefore, the subsequent two (2) table or relation demarcations take the similar information:

CREATE TABLE SavingAc ( Ac_ID INTEGER UNIQUE NOT NULL , Name CHAR , Balance INT ) ;

CREATE TABLE SavingAc ( Ac_ID INTEGER PRIMARY KEY , Name CHAR , Balance INT ) ;

Primary Keys (PKs) can restrain more than one (1) column or attribute; the code is same like the unique constrictions:

CREATE TABLE SavingAc ( Ac_ID INTEGER , CustomerRelationNumber INTEGER , Name CHAR , Balance INT , PRIMARY KEY ( Ac_ID , CustomerRelationNumber ) ) ;

A Primary Key (PK) specifies that a column or attribute otherwise a group of columns or attributes can be castoff as an exclusive identifier for rows or tuples in the table or relation. This is a straight significance of the description of a Primary Key (PK). Unique constrictions do not, in fact, offer an exclusive identifier for the reason that it does not eliminate null data. This is beneficial both for records purposes as well as for client system. For an instance, a Graphical User Interface (GUI) system application which permits transforming row or tuples data most likely wants to recognize the Primary Key (PK) of a table or relation to be capable of identifying the rows or tuples exclusively.

In the upcoming part we will be discussing the foreign keys constrictions, indexes in structured query language and as well as in what manner the indexes do function.