Post
Ask Question

SQL Constraints

Introduction

This article will teach us about SQL (Structured Query Language) Constraints and Keys. As part of our daily program, people dealing with Relational databases, mainly SQL and MySQL, must understand this while designing the databases or schemas or playing around witthemit.

Images/B1.png

Topics to be covered

  • What are constraints?
  • Keys in the 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 the transaction of the data.

Keys in DBMS

A key is an attribute or set of attributes uniquely identifying 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 keys chosen by the database designer to identify the tuples in the relation uniquely.

  1. The value of the primary key can never be null.
  2. The primary key's value must always be unique without repeating the same value.
  3. It states that a 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 attribute or set of attributes in one table refers to another table's primary key. The purpose of the foreign key is to ensure or maintain referential integrity. It is important because it ensures that all data in a database remains consistent and current.

-- 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. Similarly, 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 combines all possible attributes uniquely identifying the given relation's rows.

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

Alternate Key

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

In the Agents table:

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

Composite key

A key with more than one attribute is known as a composite key. It is also known as a compound key. The combination can also make composite keys of more than one candidate key. A composite key cannot be null.

Conclusion

Thank you for reading; I hope this article briefly explains 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 ...!


Similar Articles