Relational Database Concepts, Database Keys and Constraints

By | February 18, 2016

RDBMS Concepts

A Relational Database Management System(RDBMS) is a database management system based on the relational model introduced by E.F Codd. In the relational model, data is represented in terms of tuples(rows).

The relational database is a collection of an organised set of tables from which data can be accessed easily.

Table

A collection of database elements organized in terms of rows and columns.

What is a Record?

A single entry in a table is called a Record or Row. A Record in a table represents set of related data. For example, the above Employee table has 4 records. Following is an example of a single record.
1
Adam
34
1300

What is Field?

A table consists of several records(row), each record can be broken into several smaller entities known asFields. The above Employee table consists of four fields, ID, Name, Age and Salary.

What is a Column ?

In Relational table, a column is a set of value of a particular type. The term Attribute is also used to represent a column. For example, in Employee table, Name is a column that represent names of employee.

Name
Adam
Alex
Stuart
Ross

Database keys:

It is used to establish and identity relation between tables.
SUPER KEY
              Super Key is defined as a set of attributes within a table that uniquely identifies each record within a table. Super Key is a superset of Candidate key.
Or
  • Specifies that no two distinct records have same value. (Combination of no two records will have same data)
  • Super key may have redundant attributes.
Note: Follow this Video – https://www.youtube.com/watch?v=CsGalH0QKbs
CANDIDATE KEY
A candidate key is a field orcombination of fields that can act as a primary key field for that table to uniquely identify each record in that table.A candidate key is a sub set of a Super Keys.

In order to be eligible for a candidate key it must pass certain criteria.

  • It must contain unique values
  • It must not contain null values
  • It contains the minimum number of fields to ensure uniqueness
  • It must uniquely identify each record in the table
  • Once your candidate keys have been identified you can now select one to be your primary key
SIMPLE KEY:
      A simple key consists of a single field to uniquely identify a record.primary key is also known as simple key.
SECONDARY KEY OR ALTERNATIVE KEY :
         The attributes that are not even the Super Key but can be still usedfor identification of records (not unique) are known as Secondary Key.Alternate Key can be any of the Candidate Keys except for the Primary Key. It is a subset of candidate key.
COMPOUND KEY :
        A Combination of more than one column identifying records of a table uniquely, all the columns that take part in the combination process are Simple Key’s.
COMPOSITE KEY :

          If we use multiple attributes to createa Primary Key then that Primary Key is called Composite Key. In thisall the other columns that take part in the combination process arenot simple keys.

PRIMARY KEY :
  • It uniquely identifies each record in a database table.
  • Primary keys must contain unique values.
  • A primary key column cannot contain NULLvalues.
Non-key Attribute
          Non-key attributes are attributes other than candidate key attributes in a table.
Non-prime Attribute
          Non-prime Attributes are attributes other than Primary attribute.

SQL Constraints

SQl Constraints are rules used to limit the type of data that can go into a table, to maintain the accuracy and integrity of the data inside table.
Constraints can be divided into following two types,
  • Column level constraints : limits only column data
  • Table level constraints : limits whole table data
Constraints are used to make sure that the integrity of data is maintained in the database. Following are the most used constraints that can be applied to a table.
  • NOT NULL
  • UNIQUE
  • PRIMARY KEY
  • FOREIGN KEY
  • CHECK
  • DEFAULT
NOT NULL Constraint
         NOT NULL constraint restricts a column from having a NULL value. Once NOT NULL constraint is applied to a column, you cannot pass a null value to that column. It enforces a column to contain a proper value. One important point to note about NOT NULL constraint is that it cannot be defined at the table level.
Example using NOT NULL constraint
CREATE table Student(s_id int NOT NULL, Name varchar(60), Age int);
The above query will declare that the s_id field of Student table will not take NULL value.
UNIQUE Constraint
              UNIQUE constraint ensures that a field or column will only have unique values. A UNIQUE constraint field will not have duplicate data. UNIQUE constraint can be applied at column level or table level.
Example using UNIQUE constraint when creating a Table (Table Level) 
    CREATE table Student(s_id int NOT NULL UNIQUE, Name varchar(60), Age int);
The above query will declare that the s_id field of Student table will only have unique values and wont take NULL value.
Example using UNIQUE constraint after Table is created (Column Level) 
ALTER table Student add UNIQUE(s_id);
The above query specifies that s_id field of Student table will only have unique value.

Primary Key Constraint

Primary key constraint uniquely identifies each record in a database. A Primary Key must contain unique value and it must not contain null value. Usually Primary Key is used to index the data inside the table.
Example using PRIMARY KEY constraint at Table Level 
CREATE table Student (s_id int PRIMARY KEY, Name varchar(60) NOT NULL, Age int)
The above command will creates a PRIMARY KEY on the s_id.
Example using PRIMARY KEY constraint at Column Level
ALTER table Student add PRIMARY KEY (s_id);
The above command will creates a PRIMARY KEY on the s_id.

Foreign Key Constraint

FOREIGN KEY is used to relate two tables. FOREIGN KEY constraint is also used to restrict actions that would destroy links between tables. To understand FOREIGN KEY, let’s see it using two table.
Customer_Detail Table :
c_id
Customer_Name
address
101
Adam
Noida
102
Alex
Delhi
103
Stuart
Rohtak

Read

20 Cool funny Websites Kills Your Boredom-2016

18 Cool and Interesting Websites 2016 – Internet

The Art of Writing a Perfect Resume – Allinfi


Order_Detail Table

Order_id
Order_Name
c_id
10
Order1
101
11
Order2
103
12
Order3
102
        In Customer_Detail table, c_id is the primary key which is set as foreign key in Order_Detail table. The value that is entered in c_id which is set as foreign key in Order_Detail table must be present in Customer_Detailtable where it is set as primary key. This prevents invalid data to be inserted into c_id column of Order_Detailtable.

   Example using FOREIGN KEY constraint at Table Level

CREATE table Order_Detail(order_id int PRIMARY KEY,order_name varchar(60) NOT NULL, c_id int FOREIGN KEY REFERENCES Customer_Detail(c_id));
In this query, c_id in table Order_Detail is made as foriegn key, which is a reference of c_id column of Customer_Detail.
Example using FOREIGN KEY constraint at Column Level.
ALTER table Order_Detail add FOREIGN KEY (c_id) REFERENCES Customer_Detail(c_id);
Behaviour of Foriegn Key Column on Delete
           There are two ways to maintin the integrity of data in Child table, when a particular record is deleted in main table. When two tables are connected with Foriegn key, and certain data in the main table is deleted, for which record exit in child table too, then we must have some mechanism to save the integrity of data in child table.
  1. On Delete Cascade : This will remove the record from child table, if that value of foriegn key is deleted from the main table.
  2. On Delete Null : This will set all the values in that record of child table as NULL, for which the value of foriegn key is elected from the main table.
If we don’t use any of the above, then we cannot delete data from the main table for which data in child table exists. We will get an error if we try to do so.ERROR: Record in child table exist

CHECK Constraint

          CHECK constraint is used to restrict the value of a column between a range. It performs check on the values, before storing them into the database. Its like condition checking before saving data into a column.
Example using CHECK constraint at Table Level
 create table Student(s_id int NOT NULL CHECK(s_id > 0), Name varchar(60) NOT NULL, Age int);
The above query will restrict the s_id value to be greater than zero.
Example using CHECK constraint at Column Level
ALTER table Student add CHECK(s_id > 0);
If you found it is good, please like or comment below. Do visit for more articles.
Thank You..!!

Leave a Reply

Your email address will not be published. Required fields are marked *