Introduction To SQL Programming

Part-2

SQL Constraints

SQL constraints are used to specify rules for the data in a table.

Constraints are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the table. If there is any violation between the constraint and the data action, the action is aborted.

Think of constraints as your school/college’s security guard[He would only let students satisfying particular conditions (like having uniform and ID card on and so on) inside the premises of the institute.] or your company’s HR(whose job is to determine whether a candidate fits the company’s requirements or not).

Constraints can be column level or table level. Column level constraints apply to a column, and table level constraints apply to the whole table. Some commonly used constraints are described below:

ConstraintDescription
NOT NULLEnsures that a column cannot have a NULL value
UNIQUEEnsures that all values in a column are different
PRIMARY KEYA combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table
FOREIGN KEYPrevents actions that would destroy links between tables
CHECKEnsures that the values in a column satisfies a specific condition
DEFAULTSets a default value for a column if no value is specified
CREATE INDEXUsed to create and retrieve data from the database very quickly

PRIMARY KEY

The formal definition of primary key goes like: “Primary key is a value which is unique for every record in the table.”. Consider this analogy. Supposedly, a school teacher gives you and your friend a task to collect the details of the students interested in the annual function. You survey the necessary people and make a table containing the names of the people who are interested and their section. Your friend, being the oversmart person he is, collects the name of the students along with the answer to the questions: “How many hours can you devote to practice?” thinking that this will help the teacher shortlist the candidates that are most interested. When you both present your data to the teacher, the teacher instructs you to merge the data . If you both had made the table on SQL, and saved it on the same database, how would you go about merging the tables?

One of the simplest approach would be to use a field common to both the tables and initiate the merge(you will be learning about this later on during the course). The obvious choice for for this common field is the student name, as this parameter is common to the tables made by both of you. In practice, we call this common field the primary key. We can define a primary key,along with several other constraints we learned earlier in the example used earlier like this:

create database CodevitaLive;
create table Courses(id integer not null, title varchar(100),price integer default 0,cohortsize integer,primary key(id),unique(title),check (cohortsize>2));
insert into Courses(id, title,price,cohortsize) values(1, "HYP101",6000,100);
insert into Courses(id, title,price,cohortsize) values(2, "DSF",4000,50);
insert into Courses(id, title,price,cohortsize) values(3, "Quantum Leap",10000,10);
insert into Courses(id, title,price,cohortsize) values(4, "Bon Voyage",450,10000);
select * from Courses where price<2000 and cohortsize>100;
select "The courses whose price is above 2000 and cohort size below 100 are displayed above";
select "The courses whose price is more than 5000 or cohort size below 15 displayed below";
--the above method can be used to display text
select * from Courses where price>5000 or cohortsize<15;

Try tweaking around the values and notice the use of the constraints that we deployed. We would use the other constraints later on during the course.

Assignment 1

Create a database named matriculation exam and a table containing your name. Insert 6 subjects into the table along with your marks. You can either use MySQL (install MySQL by referring to this video).or use this online compiler. The primary key should be your name and an unique constraint should be set for the subjects. The marks inserted should be below 100 and the default marks should be 34.