How can I drop the "Unique Key Constraint" on a column of a MySQL table using phpMyAdmin?
This question is related to
mysql
First delete table
go to SQL
Use this code:
CREATE TABLE service( --tablename
`serviceid` int(11) NOT NULL,--columns
`customerid` varchar(20) DEFAULT NULL,--columns
`dos` varchar(30) NOT NULL,--columns
`productname` varchar(150) NOT NULL,--columns
`modelnumber` bigint(12) NOT NULL,--columns
`serialnumber` bigint(20) NOT NULL,--columns
`serviceby` varchar(20) DEFAULT NULL--columns
)
--INSERT VALUES
INSERT INTO `service` (`serviceid`, `customerid`, `dos`, `productname`, `modelnumber`, `serialnumber`, `serviceby`) VALUES
(1, '1', '12/10/2018', 'mouse', 1234555, 234234324, '9999'),
(2, '09', '12/10/2018', 'vhbgj', 79746385, 18923984, '9999'),
(3, '23', '12/10/2018', 'mouse', 123455534, 11111123, '9999'),
(4, '23', '12/10/2018', 'mouse', 12345, 84848, '9999'),
(5, '546456', '12/10/2018', 'ughg', 772882, 457283, '9999'),
(6, '23', '12/10/2018', 'keyboard', 7878787878, 22222, '1'),
(7, '23', '12/10/2018', 'java', 11, 98908, '9999'),
(8, '128', '12/10/2018', 'mouse', 9912280626, 111111, '9999'),
(9, '23', '15/10/2018', 'hg', 29829354, 4564564646, '9999'),
(10, '12', '15/10/2018', '2', 5256, 888888, '9999');
--before droping table
ALTER TABLE `service`
ADD PRIMARY KEY (`serviceid`),
ADD unique`modelnumber` (`modelnumber`),
ADD unique`serialnumber` (`serialnumber`),
ADD unique`modelnumber_2` (`modelnumber`);
--after droping table
ALTER TABLE `service`
ADD PRIMARY KEY (`serviceid`),
ADD modelnumber` (`modelnumber`),
ADD serialnumber` (`serialnumber`),
ADD modelnumber_2` (`modelnumber`);
The indexes capable of placing a unique key constraint on a table are PRIMARY
and UNIQUE
indexes.
To remove the unique key constraint on a column but keep the index, you could remove and recreate the index with type INDEX
.
Note that it is a good idea for all tables to have an index marked PRIMARY
.
my table name is buyers which has a unique constraint column emp_id now iam going to drop the emp_id
step 1: exec sp_helpindex buyers, see the image file
step 2: copy the index address
step3: alter table buyers drop constraint [UQ__buyers__1299A860D9793F2E] alter table buyers drop column emp_id
note:
Blockquote
instead of buyers change it to your table name :)
Blockquote
thats all column name emp_id with constraints is dropped!
For WAMP 3.0 : Click Structure Below Add 1 Column you will see '- Indexes' Click -Indexes and drop whichever index you want.
You can DROP
a unique constraint from a table using phpMyAdmin as requested as shown in the table below. A unique constraint has been placed on the Wingspan field. The name of the constraint is the same as the field name, in this instance.
If you want to remove unique constraints from mysql database table, use alter table with drop index.
Example:
create table unique_constraints(unid int,activity_name varchar(100),CONSTRAINT activty_uqniue UNIQUE(activity_name),primary key (unid));
alter table unique_constraints drop index activty_uqniue;
Where activty_uqniue is UNIQUE constraint for activity_name column.
The constraint could be removed with syntax:
As of MySQL 8.0.19, ALTER TABLE permits more general (and SQL standard) syntax for dropping and altering existing constraints of any type, where the constraint type is determined from the constraint name:
ALTER TABLE tbl_name DROP CONSTRAINT symbol;
Example:
CREATE TABLE tab(id INT, CONSTRAINT unq_tab_id UNIQUE(id));
-- checking constraint name if autogenerated
SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME = 'tab';
-- dropping constraint
ALTER TABLE tab DROP CONSTRAINT unq_tab_id;
while dropping unique key we use index
ALTER TABLE tbl
DROP INDEX unique_address;
Source: Stackoverflow.com