How to Drop Constraint From the MySQL Table

1. Create a Table With Constraints in MySQL
2. Drop Constraint From the MySQL Table

The constraint is a set of rules or restrictions prohibiting the data entry in the MySQL columns. The table in MySQL has various attributes or columns over which a user wants restrictions.

The applied barriers disable the allowance of all types of data values in the MySQL column set, providing consistency across the column. It enables only values allowed in the definition of constraint set applied.

Create a Table With Constraints in MySQL

In MySQL, users can apply the constraints on columns along with the table schema. These constraints can be of two types given below.

1. Column Level Constraint
2. Table Level Constraint

Syntax to create a table with constraints:

Create table tablemname
 attributeName1 attributeType constraint,
 attributeName2 attributeType constraint,
 ...
 attributeNameN attributeType constraint,
 TableLevelConstraint(AttributeName)

The syntax in the above code shows how to create table level and column level constraints for each. The idea behind both is to give the users privilege to add constraints as needed.

1. Column level constraint: The type of constraints like non-null and unique are defined while defining the attribute names. It means that the constraint name is after the data for the attribute is provided.
2. Table level constraint: The type of constraints gets defined at the end of the table definition. These constraints are like PrimaryKey ForeignKey and Index constraints.

List of queries before actual query on tables:

create table studentPK ( id varchar(255) not null, firstName varchar(255), lastname varchar(255), age integer, primary key (id));

The above command creates a table studentPK with attributes such as id, firstName, lastName, and age. The primary key constraint is a keyword defined in MySQL for creating the table’s primary key or unique identifier.

The syntax takes the parameters in its argument to make the attribute the primary key.

Describe studentPK;

Another query is to describe the table that gets created. The image shown below illustrates the constraint that gets entered into the fields of the studentPK table.

Drop Constraint From the MySQL Table

Query to execute the DROP constraint in MySQL:

Alter table studentPK drop primary key;

The above syntax changes the syntax of studentPk using the Alter keyword. Since the constraint is at table level, it is easy to drop at table level well.

The Alter command is the best fit while changing the table’s schema. It changes the schema of the StudentPk table by dropping the primary key from the same.

Below is the local run image for the same.

Similarly, when the same key gets used in other tables, it gets dropped by dropping the foreign key of another table.

Query to create foreign key constraint:

create table studentDept ( deptid varchar(255) not null, deptName varchar(255), id varchar(255), foreign key(id) references studentPK (id));

The above query creates a foreign key as id on another relation studentDept table. The foreign key gets formed by using the keyword foreign key.

Along with the keyword, it requires the references keyword to link the parent table to make the two tables dependent. The references keyword binds the id field of the studentPk table as a foreign key in the studentDept table.

Output can get verified in the image shown below:

When a foreign key gets created internally, a new key name for the key gets created. The statement to describe the table and see the name are as below:

SHOW CREATE TABLE studentDept;

Below is the picture of the console output to see the table structure in MySQL.

The above image says the foreign key gets created with the name studentdept_ibfk_1. Now, it is easy to drop the foreign key with the name it gets created.

Alter table studentDept drop foreign key studentdept_ibfk_1;

The above query drops the foreign key from the studentDept table. The Alter command states that the change happens at the schema level.

The foreign key gets dropped from the studentDept table. It can get rechecked using the describe command.

Points to remember before dropping the foreign key:

1. The name of the foreign key is needed beforehand. A key is required to drop the constraint from the table.

2. The drop command without knowing the key name leads to the error shown below:

   

3. The SHOW CREATE TABLE command is compulsory.