StatusNeo

A complete guide to SQL from scratch- Part-II (Constraints)

Introduction

In our previous blog, we have covered the basics of SQL(Structured Query language), DDL (Data Definition Language), and DML(Data Manipulation Language).

In this blog, we are going to discuss how to implement restrictions and modifications to the table structure.

Link to the part 1 of this blog- https://statusneo.com/sql/

Different Data Constraints

There are different data constraints that can be used while creating a table, besides the constraints of the column, type, and length.

These data constraints can be placed at the table level or the column level. Let’s discuss both of them one by one.

Table Level Constraint– Table level constraints are used when the data constraints are applied to the column that references the other columns of the table.

Column Level Constraint-When the constraints are defined with the column declaration, that is known as a column-level constraint.

List of most used Constraints

Primary Key Constraint– Primary key is used with a column that will uniquely define a row, and will remain unique throughout the table. In a real-world example, we can say that EmployeeId is the primary key of every employee in an organization. Only one primary key can be used in a table.

create table tableName (columnname datatype (size) primary key, ...)

Unique Key Constraint– Unique key is similar to the primary key. The only difference is the information in the column for each record is unique. Multiple unique keys are possible in a single table. Unique keys are used to uniquely identify a tuple in a table. Unique keys can be used as foreign keys also. Unique keys are mostly used when primary keys are not present in the table.

create table tableName (columnname datatype (size) unique, ...)

Default Value Constraint– While creating a column we assign a default value to it. While inserting a value in the table if the column value is set as empty or left blank then the default value is assigned to it.

create table tableName (columnname datatype (size) default value, ...)

Foreign Key Constraint– Foreign keys are used to define the relationship between two tables. A foreign key is a column whose values are derived from the primary key of the same attribute of some other table. A foreign key must have a corresponding primary key value in the primary key table to have meaning.

create table tableName (columnname datatype (size) default value, ...,foreign key(columnname) references tableName)

Check Integrity Constraint– They are used when we are required to enforce the integrity rules that can be evaluated based on a logical expression. These constraints can be a check like a name should not contain a particular character.

create table tableName (columnname datatype (size) default value, ...,CONSTRAINT constraintname check(expression))
For example we want to check for the gender column it should be 'M' or 'F'
CREATE TABLE employee
( id varchar(50) PRIMARY KEY,
name varchar(20),
dept varchar(30),
gender char(1) CHECK (gender in ('M','F')),
);

How to modify the structure of a table

For modifying the structure of a table ‘Alter’ command is used. There are few limitations when it comes to the modification of a table-

1. Table Name cannot be changed
2. Decrease the size of a column if table data exists and occupies a larger size

Now, let’s see the things that can be modified-

Adding a new column in a table

ALTER TABLE tableName ADD (newColumnName datatype (size));

Modifying an existing column in a table

ALTER TABLE tableName modify COLUMN columnName datatype(size);

Deleting a column in a table

ALTER TABLE tableName DROP Column columnName;

Deleting a table

DROP TABLE tableName;

Adding Constraint with the help of Alter table command

We can also add the constraint after the table is created, let’s see how to add them.

Adding a primary key-

ALTER TABLE tableName ADD PRIMARY KEY(columnName);

Adding a foreign key-

ALTER TABLE tableName ADD FOREIGN KEY(columnName) REFERENCES tableName;

Adding a check constraint-

ALTER TABLE tableName ADD CONSTRAINT constraintName Check(expression);

Dropping Constraint with the help of Alter table command

Dropping a primary key-

ALTER TABLE tableName DROP PRIMARY KEY;

Dropping a foreign key-

ALTER TABLE tableName DROP FOREIGN KEY;

Dropping a check constraint-

ALTER TABLE tableName DROP CONSTRAINT constraintName;

Conclusion

That’s it from this blog, we have covered how to use constraints in SQL. In our next blog, we will learn how to write SQL queries using SET and JOIN operations.

PART I of this blog- https://statusneo.com/sql/

Still Curious? Visit my website to know more!

For more interesting Blogs Visit- Utkarsh Shukla Author