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.
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
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'
sample_db represent the database where the table resides.
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.