Home » Programming Languages » Query Languages

How to Check all the Existing SQL Constraints on a Table?

  • Ankit Dixit 
How to Check all the Existing SQL Constraints on a Table?

In this article, we will learn about the constraints in SQL and how to check all the existing SQL Constraints in the table along with examples to understand the concept much better.

What are the Constraints in SQL?

Constraints are used for enforcing the integrity of the data in a table by specifying the conditions that must be met in order to insert data into the column or by defining rules about the values that can be stored in the columns of the table.

When a constraint is defined at the column level then it is called a column-level constraint. 

When the constraint is defined at the table level using the CONSTRAINT keyword in SQL, it’s called a table-level constraint. 

  • A table-level constraint can use multiple columns
  • A table-level constraint can be provided with some names also

What is a Unique Constraint?

The unique constraints are used for ensuring that all the values in the column are different or that each row has a unique value for the column or columns. A unique constraint is either a column constraint or table constraint that defines a rule that constrains values in a column or group of columns to be unique. 
If we insert or update a value that causes a duplicate value in the unique column, MySQL will issue an error message and reject the change.

To define a unique constraint to a column:

CREATE TABLE TABLE_NAME(
	COLUMN_NAME
);

To define a unique constraint as a table constraint:
CREATE TABLE TABLE_NAME(
	…,
	COL_NAME_DATATYPE,
	…,
	UNIQUE(col_name)
);

If we want to enforce unique values across columns, we must define unique constraints as table constraints and separate each column by a comma.

CREATE TABLE TABLE_NAME(
	…,
	col_name_3_datatype,
	col_name_4_datatype,
	…,
UNIQUE(col_name)
);

We can also assign a name to a unique constraint using the CONSTRAINT clause:

CREATE TABLE table_name(
	…,
	col_name_1_datatype,
	col_name_2_datatype,
	…,
	CONSTRAINT
);

To see the index we can use:

SHOW INDEX FROM table_name;

To remove the index we can use:

DROP INDEX

OR

ALTER TABLE table_name
DROP INDEX index_name;

To add constraints to an existing table:

ALTER TABLE table_name
ADD UNIQUE(column_list);

To add a unique constraint with the name:

ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE(column_list);

Primary Key Constraint:

The primary key constraint requires that each row has a unique value for the column or columns and it does not allow a null value.

Whenever a column is declared as a primary following things happen:

  • The column is forced to be NOT NULL
  • The column is forced to contain only unique values 
  • The index is created for the column

The table can have only one primary key.

To define the primary key constraint to a column:

CREATE TABLE table_name(
	…,
	col_name PRIMARY KEY,
	…
);

To define the primary key constraint as a table constraint:

CREATE TABLE table_name(
	…,
	col_name data_type,
	…,
	PRIMARY KEY(col_name)
);

To define a primary key for multiple columns:

CREATE TABLE table_name(
	…,
	col_name_1_datatype
	col_name_2_datatype,
	…,
	PRIMARY KEY(col_name_1, col_name_2)
);

To assign a name to the primary key constraint we have to use the table constraint:

CREATE TABLE table_name(
	…,
	col_name_1_datatype
	col_name_2_datatype,
	…,
	CONSTRAINT constraint_name PRIMARY KEY(col_name)
);

To see the index we can use:

SHOW INDEX FROM table_name;

To drop the primary key:

ALTER TABLE table_name
DROP PRIMARY KEY;

To add a primary key to an existing table:

ALTER TABLE table_name
ADD PRIMARY KEY(column_name);

To add a primary key with some name to the existing table:

ALTER TABLE table_name
ADD CONSTRAINT constraint_name PRIMARY KEY(column_name);

Foreign Key Constraint:

Foreign key constraint/ reference key constraint requires a value in one table to match the value in another table. This defines the relationship between 2 tables and enforces referential integrity.

To create a foreign key, at the table level we write FOREIGN KEY keywords followed by REFERENCES keyword followed by the name of the related table and the name of the related column in parenthesis. 

Actions:

When a row from the primary table is updated or deleted:

  • If we use the CASCADE option, then the delete is cascaded to the related rows in the foreign key table.
  • If we use the SET NULL option, then the foreign key column of the foreign key table is set to null

To define the foreign key constraint:

CREATE TABLE table_name(
col_name_1_datatype,
col_name_2_datatype,
…,
[CONSTRAINT constraint_name]
FOREIGN KEY
REFERENCES
[ON DELETE {CASCADE | SET NULL| RESTRICT}]
[ON UPDATE {CASCADE | SET NULL| RESTRICT}]
);

To see the index we can use:

SHOW INDEX FROM table_name;

To drop foreign key:

ALTER TABLE table_name
DROP FOREIGN KEY constraint_name;

To add a foreign key to an existing table:

ALTER TABLE table_name
ADD FOREIGN KEY (column_list) REFERENCES table_name(column_list)
[actions];

To add a foreign key with some name to the existing table:

ALTER TABLE table_name
ADD CONSTRAINT constraint_name FOREIGN KEY (column_list) REFERENCES table_name (column_list)
[actions];

SQL query to display all the existing constraints on the SQL table. There can be a situation when we want to display all the existing constraints on the SQL table. In this section, we will be studying the whole process of how to display existing constraints on an SQL table.

Following is the step-wise procedure to check an existing constraint on the SQL table:

Step 1: Create a database using the command given below:

Query –  

CREATE DATABASE students;

Step 2: Use the student’s database by executing the command given below:

Query – 

USE students;

Step 3: Create a table student_info for storing the information of the students in the database students. The table will have two column id, name, and marks. 

Query –

CREATE TABLE student_info(
	id int,
	name varchar(10),
	marks int
);

Step 4: Display the constraints that are applied to the students_info table. In this, we will be displaying the name of the constraint as well as the type of constraint of all the existing constraints.

The syntax for the same can be given as follows:

SELECT INFORMATION
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_NAME='TABLE_NAME';

Query –

SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_NAME='student_info';

The output displays no constraints since the table has no constraints currently and therefore no row is displayed in the output.

Step 5: we will now alter the column id by using the alter clause and will set it to NOT NULL. we have to do it since it is a prerequisite for setting the id as a primary key.

Query –

ALTER TABLE student_info ALTER 
COLUMN id INT NOT NULL;

Step 6: in this step, we will now add a constraint to the primary key, let’s say const1 to the column id by using the alter clause.

Query –

ALTER TABLE student_info ADD CONSTRAINT 
const1 PRIMARY KEY (id);

Step 7: in this step, we will now display the current constraints that are applied on the table named student_info

Query –

SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_NAME='student_info';

Note: the table that we have only had 1 constraint which is the primary key constraint and hence only one row is displayed at the output.

Step 8: now we will add a check constraint and will name it check_marks to the column marks by using the alter clause in SQL.

Query – 

ALTER TABLE student_info ADD CONSTRAINT check_marks 
CHECK (marks IN(75,80,90));

Step 9: in this step, we will display the current constraints that are applied to the table student_info.

Query –

SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_NAME='student_info';

Note: here two rows will be displayed since the table has two constraints namely, the primary key constraint and the check constraint.

Step 10: in this step, we will add a unique constraint which will be named uni to the column name by using the alter clause of the SQL.

Query –

ALTER TABLE student_info ADD CONSTRAINT 
uni UNIQUE(name);

Step 11: in this step, we will display the current constraints that are applied to the table student_info.

Query –

SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_NAME='student_info';

Note: here, three rows will be displayed since the table has 3 constraints which are the primary key, the check constraint, and the unique constraint.

Conclusion 

This is it for this article. I hope you got a clear understanding of the constraints in SQL as well as how to check all the existing constraints in SQL on the table.

Tags:
Tags:

Related Content