Database Constraints

← Go back to Database Chapter

Constraints are the rules which can be applied to the data getting inserted in the table or while updating the table. The types of constraints are given below:

  • Not null constraint

  • Unique constraint

  • Primary key constraint

  • Foreign key constraint

  • Check constraint



The not null constraint makes sure that a field is never empty or null, but is always assigned a value. It also ensures that null values are not accepted by a column.

  • Whenever the programmer has not provided a value for any columns, then null will be automatically inserted by the DBMS

  • Null means value is not available

  • Null is not equivalent to zero or space or any other thing

  • If you do not want another value for any given column and force the user to supply the value then you can apply not null constraints

  • Usage of the Not Null constraint:
    CREATE TABLE students (sid INT, sname, CHAR (10) NOT NULL , totalfee DOUBLE NOT NULL_ _ _ _ _ _ _ _);

Question: What will happen when we try to insert a record into students table without total fee?
Answer: An error will occur as it is not null specified and you have to provide a value.

If you want to provide only unique values for any given column then you can apply the unique constraint.

  • Usage of Unique constraints:
    Create table students S. ID int not null unique, S. Name Char (10) not null, Email char (10) not null unique, Phone long unique, );

Question 1: What will happen when two students provide the same number?
Answer: The second student’s record insertion will fail.

Question 2: Can I have multiple null values for the phone number columns?
Answer: Yes.

The Primary key constraints checks both not null and unique constraints. It should have a unique value and cannot be null. It provides a unique identification for every row in the table.

Primary Key = Not Null + Unique

Therefore, it has properties of both not null and unique constraints.

  • Usages:
    Create table students ( sid INT PRIMARY KEY, sname CHAR(10) NOT NULL, email CHAR (10) NOT NULL UNIQUE, phone LONG NOT NULL UNIQUE,_ _ _ _ _ _ _ _ _);

The table should contain only one primary key.

There are two types of primary key:

  • Simple primary key

  • Composite primary key

Simple primary key:

When you specify the key for a single column then it is called simple primary key.

Example:
Create table students (sid INT PRIMARY KEY, sname CHAR (10) NOT NULL UNIQUE_ _ _ _ _ _ _);

Composite Primary Key:

When you specify the primary key for a combination of two or more columns, then it is called composite primary key.

Example:
Create table, accounts ( Bcode int, acc no int, atype int, bal double not null, primary key (bcode, atype, accno));

Only for balance,
Select balance of accounts where balance code = 0047 and account types = 01 and account no = 530255
Output: Full Accounts No: 004701530255

The Foreign key constraint is used to the establish the relationship among the tables.

Customers
Customer ID Customer Name Email Phone
       
       
       
       
       
       

Accounts
Customer ID Account Type Account Type Amount Balance
         

Transactions
Branch Code Account Type Account No Trans. ID Trans. Date Trans. Type Amount Total
             

Usages:

Create table customers (customerid, interest int primary key, customername char (10) not null);

primary key constraints in sql

Create table accounts (customer int references customers, branch code int, account type int, balance double not null, primary key (branch code, account type, account no));

foreign key constraints in sql

Create table transactions (branchcode int, accounttype int, account no int, TxID int, Txdate date, Tx type char (2), amount double, foreign key accounts (branch code, account type, account no));

Note: A Table can contain many foreign keys but only one primary key.

The Check constraint checks the specified condition on the specified columns. These conditions have to be met by the column. However, it can be placed only on one particular table, and not all the tables.

Usage:
Create table students (student ID int primary key, student name char (10) not null, total fee double check total fee >=1500; fee paid double check fee paid double check fee paid >= 1000, city character (20) check city in ('Banglalore', 'mysore'));

check constraints in sql

  • It will make sure that total fee inserted should be greater than or equal to 1500

  • It will make sure that the fee paid should be greater than or equal to 1000.

  • The city name can only be Pune or mysore.

  • 31 January 2011

    • ZIPCODE : Substr (Zipcode, 1, 3) 11 '-' substr (zipcode,4)
      To- date (‘20-Jul-11’ ‘DD-MONTH-YYYY’)
      Ltrim - side space
      Ltrim - left side space
      Diagram -20

Select sum (fee paid) 'total' from student group by branch order by total;

select all sql command

group by and order by sql command

group by and order by sql command

  • Always use Desc and asc after the order by and column name;

  • Occupation - create certified professional