How can i drop a unique constraint which do not have any id from the column in sql

Question

create table frontend_employee(
employee_id int primary key,
employee_name varchar(50),
employee_role varchar(50),
employee_sarlary int default 25000,
employee_boss_id int unique,
foreign key(employee_boss_id) references frontend_boss(boss_id)
);

as you can see I haven't given any ID to that UNIQUE constraint for giving ID to that constraint I should have used another method which is

create table frontend_employee(
employee_id int primary key,
employee_name varchar(50),
employee_role varchar(50),
employee_sarlary int default 25000,
employee_boss_id int,
CONSTRAINT UC_employee UNIQUE (employee_boss_id),
foreign key(employee_boss_id) references frontend_boss(boss_id)
);

I have tried every method present on Google but all are them need a NAME of the constraint but i haven't given any name to the UNIQUE constraint I can not find any method to remove that UNIQUE constraint from that column

Query present on the internet:-

ALTER TABLE table_name DROP CONSTRAINT unique_constraint;

Answer 1

In your case you can't drop only the unique constraint because it is needed in a foreign key constraint. You have to drop the foreign key constraint as well.

Consider the following data example

create table frontend_boss(
    boss_id int unique );


create table frontend_employee(
  employee_id int primary key,
  employee_name varchar(50),
  employee_role varchar(50),
  employee_sarlary int default 25000,
  employee_boss_id int unique,
  foreign key(employee_boss_id) references frontend_boss(boss_id) );
  

 show create table frontend_employee;

 CREATE TABLE `frontend_employee` (
  `employee_id` int(11) NOT NULL,
  `employee_name` varchar(50) DEFAULT NULL,
  `employee_role` varchar(50) DEFAULT NULL,
  `employee_sarlary` int(11) DEFAULT 25000,
  `employee_boss_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`employee_id`),
  UNIQUE KEY `employee_boss_id` (`employee_boss_id`),
  CONSTRAINT `frontend_employee_ibfk_1` FOREIGN KEY (`employee_boss_id`) REFERENCES `frontend_boss` (`boss_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ;

The unique constraint has the autogenerated name employee_boss_id

To drop

alter table frontend_employee drop constraint employee_boss_id;

ERROR 1553 (HY000): Cannot drop index 'employee_boss_id': needed in a foreign key constraint

Drop unique constraint and the foreign key

alter table frontend_employee drop constraint employee_boss_id, drop constraint  frontend_employee_ibfk_1;
Query OK, 0 rows affected (0.045 sec)
Records: 0  Duplicates: 0  Warnings: 0

 show create table frontend_employee;

 CREATE TABLE `frontend_employee` (
  `employee_id` int(11) NOT NULL,
  `employee_name` varchar(50) DEFAULT NULL,
  `employee_role` varchar(50) DEFAULT NULL,
  `employee_sarlary` int(11) DEFAULT 25000,
  `employee_boss_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`employee_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;