Java

Database Constraints

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);

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));

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'));

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