SQL Constraints

Introduction

In this article, we are going to learn about SQL (Structured Query Language) Constraints and Keys. As part of our daily program people who are dealing with Relational databases mainly SQL, MySQL needs to have good understanding about this while designing the databases or schemas or playing around with it.

Topics to be covered

  • What are constraints?
  • Keys in Database management system (DBMS)
  • Creating a demo table with data
  • Primary Key
  • Foreign key
  • Candidate key
  • Super key
  • Alternate key
  • composite key

What are constraints?

Constraints are used to limit which type of data must be stored in the database. We use SQL constraints to specify the rules for the data in a table. SQL constraints increase the accuracy and reliability of the data stored in the database. Constraints make sure that there is no violation in terms of transaction of the data.

Keys in DBMS

A key is an attribute or set of attributes that uniquely identifies any record or tuple i.e one row from the table.

Creating a demo table with data

-- Table Creation
CREATE TABLE "AGENTS"
(
   "AGENT_CODE" NVARCHAR(10) NOT NULL PRIMARY KEY,
   "AGENT_NAME" NVARCHAR(30),
   "WORKING_AREA" NVARCHAR(30),
   "COMMISSION" bigint,
   "PHONE_NO" NVARCHAR(20),
   "COUNTRY" NVARCHAR(20)
);
-- Adding Data
INSERT INTO AGENTS VALUES ('A007', 'Ramasundar', 'Bangalore', 1, '077-25814763', null);
INSERT INTO AGENTS VALUES ('A003', 'Alex ', 'London', 2, '075-12458969', '');
INSERT INTO AGENTS VALUES ('A008', 'Alford', 'New York', 3, '044-25874365', '');
INSERT INTO AGENTS VALUES ('A011', 'Ravi Kumar', 'Bangalore', 4, '077-45625874', '');
INSERT INTO AGENTS VALUES ('A010', 'Santakumar', 'Chennai', 5, '007-22388644', null);
INSERT INTO AGENTS VALUES ('A012', 'Lucida', 'San Jose', 6, '044-52981425', '');
INSERT INTO AGENTS VALUES ('A005', 'Anderson', 'Brisban', 7, '045-21447739', null);
INSERT INTO AGENTS VALUES ('A001', 'Subbarao', 'Bangalore', 8, '077-12346674', '');
INSERT INTO AGENTS VALUES ('A002', 'Mukesh', 'Mumbai', 9, '029-12358964', null);
INSERT INTO AGENTS VALUES ('A006', 'McDen', 'London', 10, '078-22255588', '');
INSERT INTO AGENTS VALUES ('A004', 'Ivan', 'Torento', 11, '008-22544166', null);
INSERT INTO AGENTS VALUES ('A009', 'Benjamin', 'Hampshair', 12, '008-22536178', '');

Primary Key

A primary key is one of the candidate key chosen by the database designer to uniquely identify the tuples in the relation.

  1. The value of primary key can never be null.
  2. The value of primary key must always be unique value without being repeated the same value.
  3. It states that column is a mandatory field.
  4. Primary key is not compulsory but recommended.
  5. It does not contain any duplicate values.
-- Agent Code is Primary Key as per creation of the table
< AGENT_CODE >

Foreign Key

A foreign key is used to link two tables together. An attributes or set of attributes in one table that refers to the primary key in another table. The purpose of the foreign key is to ensure or maintain referential integrity and it is important because it ensures that all data in a database remains consistent and up to date.

-- Foreign Key in the SQL management system which has the relation with other table
< User_Id >

Candidate Key

A Candidate key is an attribute or set of an attribute that can uniquely identify a tuple. In similar, a candidate key is a minimal super key; or a super key with no redundant attributes.

Candidate keys are not allowed to have Null values.

-- Candidate keys in sql management system
< Agent_Code >
< User_Id >

Super Key

A Super key is a combination of all possible attributes that can uniquely identify the rows in the given relation.

  • A super key is a superset of a candidate key.
  • A table can have many super keys.
  • A super key may have additional attribute that are not needed for unique identity.
--Super keys
< Agent_Code >
< User_Id >
< Commission, phoneno >
< User_Id, Commission >
< phoneno, User_Id >

Alternate Key

The perfect description about the alternate keys are out of all the candidate keys, only one gets selected as primary key, remaining keys are known as alternate keys.

In the Agents table:

  • Agent_Code is the only one suited for primary key.
  • Rest of all other attributes are considered as alternate keys.

Composite key

A key that has more than one attributes is known as a composite key. It is also known as compound key. Composite key can also be made by the combination of more than one candidate key. A composite key cannot be null.

Conclusion

Thank you for reading, I hope this article gives you a brief idea about SQL Views with examples and code samples.

Please let me know your questions, thoughts, or feedback in the comments section. I appreciate your feedback and encouragement.

Keep learning ...!