Constraints in sql

In databases when working with SQL, constraints play a crucial role. Constraints are predefined rules and restriction impose on any column in particular table by preventing invalid data from being inserted, updated,

or deleted from the table ensuring data accuracy and reliability throughout the database. If there is violation of constraint, then action will fail. You can have multiple constraints in single table

In constraints you can define constraints at column level as well as table level

Types of  SQL Constraints

SQL has an array of constraint types that have different reasons maintain database integrity.     

There are 6 types of constraints

1)  Primary Key Constraints

2)  Foreign Key Constraint

3)  Unique Constraint

4)  Check Constraint

5)  Not Null Constraint

6) Exclusion Constraint

Primary key Constraint:

Primary key is key which is uniquely identifier for  each entry in a table. Primary key constraint is constraint that uniquely identifies each row in a table and does not allow any NULL values for the particular column. Also, it internally creates an index for a table and orders the data internally that helps us to improve query performances.

In SQL every table strictly restricted to have only 1 primary key 

For example, in a table for storing Customer details, the Customer ID can be set as the Primary Key to uniquely identify each Customer.

 Foreign Key Constraint:

In RDBMS a Foreign Key is used to link two tables by referring to the Primary Key of another table. This constraint ensures that the data entered in the foreign key column must match the values in the primary key. Foreign key helps us to maintain data consistency and data integrity between tables. A table can have multiple foreign keys

  As an example, to explain foreign key constraint we need two table one is Student details consider this table as parent table and another is Student mark consider this as a child table.

The Roll no in the student mark table can be used as a foreign key

That refers to the roll no in student table this says that each student is associated with valid roll no

 Unique Constraint:

Like Primary Key, the Unique Constraint helps us to identify data contained in a specified column or group of columns is unique. However, unlike Primary Keys, the Unique Constraint allows 1 NULL values.

For example, in an employee details table, every employee should have a unique email id. the constraint ensures that no two employees have the same email id. suppose ,by mistakenly you have entered the same email id which is already exist in primary key column , while executing this query, you will get the error message that this email id already exists

Check Constraint:

A Check Constraint is a rule that helps to limit the data that can be entered into a specified column.

If the BOOLEAN _Expression returns true then the CHECK Constraint allows the value, otherwise it doesn’t.

Check Constraints can be used for various conditions, such as defining a valid range of values, validating data input formats, or ensuring that certain column values meet specific criteria. An example of using a Check Constraint can be found when storing Age in a table.

You can add a constraint that ensures the value entered in age column falls within a valid range, e.g. between 18 to 50

Not Null Constraint:

This constraint makes sure that a specified column cannot contain NULL values and that data in column is always present and valid by insertion even while doing updating

For example, in an employee details table, you might want to ensure that all employees have a valid employee name entered in their respective fields. we apply a Not Null constraint to a column

we must ensure that no employee can be entered with a NULL employee name

Exclusion Constraint

Exclusion Constraint is a bit difficult method which is used to check the values in one row against other rows which included in the same table.

 Exclusion constraints are used to specify an expression returning a true/false outcome and data can be inserted only when the expression evaluate to false record means indicating that data does not already exist

We can conclude that, to maintain the data integrity and consistency right through database, the types of constraint and its purpose in sql is very essential

Implementation and Modification of constraints in SQL

Constraints in SQL are important to make sure of the reliability and accuracy of the data in the database. To manage the database properly adding, modifying, deleting constraints plays significant role

We will learn how to add, change, and drop the constraint step by step

Adding a constraint in SQL

To add a constraint, you can either add it when we are creating a table, or we can use the `ALTER TABLE` statement to add it to an existing table here we can explain with some example

Primary Key Constraint: Creating the constraint table level

CREATE TABLE Customer (Customer ID INT PRIMARY KEY, Name VARCHAR (255) NOT NULL, Email id VARCHAR(255) UNIQUE );

 Foreign Key Constraint: Adding a constraint to an existing table:

ALTER TABLE Customer ADD FOREIGN KEY (Department ID) REFERENCES Department (Department ID);

Unique Constraint: Specifying the constraint during table creation:

CREATE TABLE Employee (Customer ID INT PRIMARY KEY, Name VARCHAR (255) NOT NULL, Email Id VARCHAR(255) UNIQUE );

 Check Constraint: 

Adding Check Constraints to existing column

ALTER TABLE customer ADD CHECK (age <= 18 AND age >= 50);

Not Null Constraint:

Specifying the constraint during table creation:

CREATE TABLE Customer (Customer ID INT PRIMARY KEY, Name VARCHAR (255) NOT NULL, Email Id VARCHAR (255) UNIQUE);

Changing constraints in SQL

SQL provides various ways to change constraints, like altering a constraint , dropping a constraint and recreating it with new requirements. To change existing constraints, you will use the `ALTER TABLE` statement

Altering Constraint in sql

After a constraint has been applied to a table, sometime  you might need to modify it. SQL allows you to alter check constraints.

 Altering a Check Constraint: 

You need to create a new constraint with updated condition for that   first you need to drop existing check constraint for modification ensuring that new constraint takes its place in database

Example:

 Lets consider one basic example, if you initially limited employee age to a range between 18 and 50, you can extend it up to 49 If we insert age 60 then value should not get inserted because we gave range of 18 to 50

ALTER TABLE STUDENT DROP CONSTRAINT CK_STUDENT Age Range.

ALTER TABLE Student ADD CONSTRAINT employee Age CHECK

(Age >= 18 AND Age <= 50);

Dropping a constraint in SQL

To remove a constraint from the table, you can use the `ALTER TABLE` statement along with the `DROP CONSTRAINT` clause.

Lets see how to use drop constraint clause considering the example of student table and customer table for Primary Key Constraint, Foreign Key Constraint,Unique Key Constraint,Check constraint, Not Null Constraint.

so that we can access data efficiently , modify easily ,and remove data which is currently not required.

 Examples:

1.   Dropping a Primary Key Constraint:

ALTER TABLE STUDENT DROP CONSTRAINT PK_STUDENT_ID

2.   Dropping a Foreign Key Constraint:

ALTER TABLE Customer DROP CONSTRAINT FK_Customer_DepartmentID;

3.   Dropping a Unique Constraint:

ALTER TABLE Customer DROP CONSTRAINT UQ_ Customer Email Id;

4.   Dropping a Check Constraint:

ALTER TABLE Student DROP CONSTRAINT CK_Student_GradeRange;

5.   Dropping a Not Null Constraint:  To drop not null constraint from a column you need to alter its definition to permit null values

     ALTER TABLE TEST2 ALTER COLUMN RollNo INT NULL;

     To maintain the data integrity and consistency in your database is  very important so that one should have good knowledge of implementing and modifying the constraints in sql