MySQL Drop Table Constraint

Table constraints are a compelling feature that allows us to create rules and configurations that govern the data and the functionality of a database table.

Although it may differ on the constraint on a given table, the role of most constraints is to ensure data accuracy and reliability by defining specific requirements that must be met whenever data is added or modified in the table.

It is good to keep in mind that both column-level and table-level constraints are all part of the SQL table constraints.

However, as with all database objects, you might need to remove the table constraints to remove the rule enforced by its definition.

This post will use the MySQL database to show you how to drop an existing constraint from a given database table.

Setting up Sample Table

The first step is to create a table that contains a constraint. Feel free to skip this section if you already have a table with which you wish to work.

For our case, we will create a table that stores log information. The provided table statement is as shown below:

create table log_info(
    id int auto_increment not null primary key,
    server_name varchar(50) not null,
    version varchar(50) not null,
    db_conn varchar(50) not null,
    connections int not null,
    status_code int,
    status_string varchar(50),
    request varchar(50) not null
);
insert into
    log_info(
        SERVER_NAME,
        VERSION,
        DB_CONN,
        CONNECTIONS,
        STATUS_CODE,
        STATUS_STRING,
        REQUEST
    )
VALUES
    ('iis', '2.4.57', 'MySQL', 8, 334, 'OK', 'DELETE'),
    ('iis', '2.4.58', 'Oracle', 5, 449, 'OK', 'PUT'),
    ('iis', '2.4.58', 'MySQL', 9, 487, 'OK', 'POST'),
    (
        'apache',
        '2.4.57',
        'PostgreSQL',
        0,
        197,
        'Error',
        'DELETE'
    ),
    ('iis', '2.4.59', 'MySQL', 6, 498, 'OK', 'PUT'),
    (
        'nginx',
        '2.4.57',
        'PostgreSQL',
        10,
        284,
        'Redirect',
        'PUT'
    ),
    ('nginx', '2.4.58', 'Oracle', 2, 242, 'OK', 'DELETE'),
    ('apache', '2.4.59', 'MySQL', 7, 496, 'Error', 'GET'),
    ('nginx', '2.4.57', 'Oracle', 8, 448, 'Redirect', 'GET'),
    ('iis', '2.4.57', 'PostgreSQL', 8, 398, 'Error', 'GET');
    
SELECT * FROM LOG_INFO LF;

In the example query above, we define a table called log_info with the corresponding columns. The columns contain specific constraints such as NOT NULL, and PRIMARY KEY.

The resulting table is as shown:

MySQL Drop Constraint

To drop a constraint from an existing table, we use the ALTER TABLE command followed by the name of the constraint we wish to remove.

For example, suppose we wish to remove the PRIMARY KEY constraint from the above table. we can use the statement shown below:

ALTER TABLE log_info DROP FOREIGN KEY `PRIMARY'

The command above should remove the PRIMARY KEY constraint from the table.

To view all the constraints available in a given table, we can use the statement as shown:

select *
from information_schema.key_column_usage
where constraint_schema = 'sample_db'

Where the sample_db represent the database where the table resides.

Conclusion

In this post, you learned how you can use the ALTER TABLE command in MySQL to remove an existing table constraint in a single query.