SQL Constraints

What is constraints?

  • A set of rules are defined to enforce the database integrity, is called as Constraints.
  • The main purpose of constraint is to permit or prohibit the values in the columns.

    • Following are the Types of Constraint,
    1. Primary Key
    2. Foreign Key
    3. Unique Key
    4. Check Constraint
    5. Not Null

  • The above constraints are associated only with the tables.
  • These constraints are applied to the table at the time of table creation.

Primary Key
  • It identifies each row or record uniquely in a database table.
  • It cannot have NULL values.
  • A table can have only one primary key.
  • It contains unique values.
  • If a table has a primary key, then you cannot have two records of same value.

    • For Example: Empid INT PRIMARY KEY,

  • In the above example, there is one Employee table where Empid have a Primary Key.
  • When multiple fields are used as a primary key, is called as Composite key.

    • Syntax: PRIMARY KEY (column1, column2);
Foreign Key
  • Foreign key is a logical rule about values in multiple columns or in tables.
  • It is a set of columns in a table.
  • It requires to match at least one primary key of a row in another table.
  • It defines the relationship between the tables.
  • The 'references' keyword is used while referencing the column into the another table.

    • For example: Empid INT PRIMARY KEY, FOREIGN KEY (Empid) references Department (Empid);

  • In the above example, there are two tables Employee and Department. The 'Empid' in Employee table has a primary key. In Department table, it takes reference of 'Empid' from Employee table using Foreign key with the keyword 'references'.
Unique Key
  • It prevents two records from having identical values in a particular column.
  • It identifies each record uniquely in a database table.
  • Primary key and Unique key both provide a guarantee of uniqueness for a column.
  • Primary key has a unique constraint automatically defined on it.

    • Note: Important thing to understand about the primary key and unique key, is that a table can have many unique constraints, but only one primary key can have per table.

  • Unique key is declared with 'NOT NULL' constraint at the time of creating a table.
  • It accepts only one NULL value.
  • It is a unique non-clustered index.
Check Constraint
  • It enables a condition to check the value being entered into a record.

    • For example: Age INT NOT NULL CHECK (Age >= 19)

  • Multiple Check constraints are used in a single column.
  • It returns true or false result based on the logical operator according to Boolean (logical) expression.

Null

  • Null is a reserved keyword in SQL that indicates a data value does not exist in the database.
  • It identifies the Null special marker.
  • Null introduced by E. F. Codd, Creator of the relational database model.
  • A Null value indicates that the value is unknown, not applicable and has no value, but it does not mean that it has a zero value or a field which contains spaces.
  • It is used to represent a missing value.

    • For example:

    CREATE TABLE employee(empid INT NOT NULL,
                                            ename CHAR NOT NULL,
                                            age INT NOT NULL,
                                            city CHAR(25),
                                            phone_no VARCHAR(20),
                                            PRIMARY KEY (empid));

  • In the above example, NOT NULL indicates that the column should accept explicit value of the given data type. There are two columns 'address' and 'phone_no' which are not assigned with the NOT NULL, which means that these columns could be NULL.
  • Null is not a data value, but it is a marker of an absent value.
  • It can cause problems when selecting data, because when comparing an unknown value to any other value, the result is always unknown and not included in the final results.
  • IS NULL or IS NOT NULL is used to check for a NULL value.

    • Example of IS NOT NULL:

    <employee> Table

    empidenameagecityphone_no
    001ABC28Pune12345678
    002XYZ30Mumbai54687901
    003PQR35Kolhapur

    SELECT empid, ename, age, city, phone_no
    FROM employee
    WHERE phone_no IS NOT NULL;


    Output:
    empidenameagecityphone_no
    001ABC28Pune12345678
    002XYZ30Mumbai54687901

    Example of IS NULL:

    SELECT empid, ename, age, city, phone_no
    FROM employee
    WHERE phone_no IS NULL;


    Output:
    empidenameagecityphone_no
    003PQR35Kolhapur

  • All aggregate function (min, max, sum, count) ignores the NULL values except count() function.

When not to use NULL?

  • NULL is not specify explicitly in SELECT statement. For eg. SELECT NULL.
  • NULL is not specify explicitly as an operand of scalar expression.
    • For example, X + NULL, it is illegal.
  • NULL is not specify explicitly as an operand of a conditional expression.
    • For example, WHERE a = NULL, it is illegal.
▲