Unique table constraint

Question

I have a table as follows:

CREATE TABLE `b` (
  `b1` int(11) DEFAULT NULL,
  `b2` int(11) DEFAULT NULL,
  UNIQUE KEY `b1` (`b1`,`b2`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Here, the unique table constraint is given for 2 fields. But, in the show create table query, it is like UNIQUE KEY b1 (b1,b2) Also the first field b1 acts as a unique key in which b2 is combined.

I could drop the constraint of b1 but not b2:

mysql> alter table b drop index b2;
ERROR 1091 (42000): Can't DROP 'b2'; check that column/key exists

mysql> alter table b drop index b1;
Query OK, 0 rows affected (0.41 sec)
Records: 0  Duplicates: 0  Warnings: 0

Answers needed for :

1. Why this unique constraint is applied only for one field(first mentioned field)?
2. Is the structure of unique table constraint like that?
3. Any attachments of reference links is much needed

Thanks in advance

Answer 1

In your case b1 just name for UNIQUE KEY. Because you give not name for constraint at create table statement it named implicitly by first column name (b1). It steel check for uniqueness for pair b1 and b2 For create named key you can use next statement:

create table b (
  b1 int, b2 int, 
  unique key b1b2_uk (b1,b2)
);

Here created unique key named b1b2_uk

Look example here

If you want to remove constraint you should use it's name like:

alter table b drop index b1b2_uk;

For change constraint you need to drop exist and create new one like:

alter table b 
    drop index b1b2_uk,
    add primary key b1_pk (b1); 

Look fiddle on SQLize.online

Answer 2

Perhaps it would be easier to understand if the table looked like this:

CREATE TABLE `b` (
  `b1` int(11) DEFAULT NULL,
  `b2` int(11) DEFAULT NULL,
  UNIQUE KEY `my_unique_index` (`b1`,`b2`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

alter table b drop index my_unique_index;

Get it?