There is nothing unexpected in this behavior.

When you create `test_a_b` table, indexes on `a` and `b` columns are added automatically to comply with foreign key requirements. If you check the table structure at this point, it looks like this:

CREATE TABLE `test_a_b` (

  `a` int(11) NOT NULL,

  `b` int(11) NOT NULL,

  KEY `ref_a` (`a`),

  KEY `ref_b` (`b`),

  CONSTRAINT `ref_a` FOREIGN KEY (`a`) REFERENCES `test_a` (`id`),

  CONSTRAINT `ref_b` FOREIGN KEY (`b`) REFERENCES `test_b` (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

(note `ref_a` and `ref_b` keys).

Then, when you add the primary key, `ref_a` key is no longer needed and gets automatically dropped. The table looks like this:

CREATE TABLE `test_a_b` (

  `a` int(11) NOT NULL,

  `b` int(11) NOT NULL,

  PRIMARY KEY (`a`,`b`),

  KEY `ref_b` (`b`),

  CONSTRAINT `ref_a` FOREIGN KEY (`a`) REFERENCES `test_a` (`id`),

  CONSTRAINT `ref_b` FOREIGN KEY (`b`) REFERENCES `test_b` (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

It is expected and documented behavior (the automatic drop is better documented in MySQL manual, while MariaDB KB could use some additions).

But then, you're trying to drop the primary key. It would mean there would be no key on `a` column anymore, and foreign key needs it, hence the problem.

This behavior exists in MariaDB 5.5-10.3 and MySQL 5.6/5.7. MySQL 8.0 (at least 8.0.3 which I have handy), doesn't throw the error, it allows the PK to be dropped, but it also silently drops both foreign keys along with it, so it's hardly a proper fix. However, it hints that some attempt has been made to change the behavior, so I'm assigning it to marko to decide if we want it to be changed on MariaDB. If not, please re-assign it to greenman for extending documentation in regard to automatic key creation/removal.

Update: 8.0.12 is back to the old behavior, it also doesn't allow to drop the PK, so I guess it was just a flaw in an early version.

I see. But I'd like to point out that it is extremely non-obvious that you cannot drop an object that you just created. With no intervening command I'd assume there was nothing that would be broken by it going away, because everything had been fine before I created it. The error message also doesn't help at all.

I agree with both points. The error messages regarding foreign keys are notoriously obscure, and the legacy behavior itself is not obvious. I'd be happy if something got changed for the better, but we should be careful about deciding what exactly is "better". It's not obvious in this case. We can't get rid of automatic creation of indexes, it would have broken backward compatibility too much without real gain; I'm not sure if we can get rid of automatic removal of indexes, given that the server ls leaning towards forbidding duplicate indexes; And I don't know if we can go as far as automatic creating a missing index upon dropping an existing one, it might even increase the obscurity.

Additional note about MySQL 8.0: 8.0.12 is back to the old behavior, it also doesn't allow to drop the PK, so I guess it was just a flaw in an early version. I'll update my previous comment.

By the way, just for consideration, how I bumped into it. I had a table X with two columns, both in its primary key. Basically, very similar to the testcase, each row would constitute certain relation between rows in two other tables. Then I wanted to add a third column to X and also add it to the primary key. Adding the column went fine, but then dropping the primary key failed with no meaningful explanation.

I do not think that it is feasible to change this without a significant refactoring of the FOREIGN KEY code. Some of that could happen in MDEV-16417.

The InnoDB-internal processing of FOREIGN KEY constraints require that indexes can be exploited. If we move this processing outside storage engines, then the requirement could be lifted, and we could allow table scans to occur during FOREIGN KEY processing.

To fulfil the InnoDB requirement, adding FOREIGN KEY constraints also implicitly adds indexes. If I remember correctly, I originally implemented this around 2004. I was able to find a follow-up fix that is in MySQL 4.1.12. Before the indexes were implicitly added, the request to add a FOREIGN KEY would fail, typically with an obscure error message, and a more detailed message being written to the server error log.

These implicitly added indexes can be implicitly dropped when an index is explicitly added, such as the PRIMARY KEY in this case.

Note that DROP PRIMARY KEY is allowed if it is combined with something that allows the FOREIGN KEY checks to work:

alter table test_a_b drop primary key, add key(a);

Alternative:

alter table test_a_b add column c int not null;

alter table test_a_b drop primary key, add primary key(a,b,c);

(Note that replacing the primary key can be performed with LOCK=NONE (online), while DROP PRIMARY KEY alone will require a lock.)
One more alternative:

alter table test_a_b drop primary key, drop foreign key ref_a;

(Note that DROP CONSTRAINT ref_a is not allowed, even though it was created as ADD CONSTRAINT ref_a. This would hopefully be fixed in MDEV-16417.)

Now that MDEV-17187 has been fixed, I can suggest yet another alternative:

SET foreign_key_checks=0;

alter table … drop primary key;

SET foreign_key_checks=1;

Should the index be needed for FOREIGN KEY constraint enforcement, then InnoDB will reject such DML operations. The table will remain readable.

I think that as alter table add foreign key (or create table) automatically creates missing indexes, then similarly a missing index should be created after the primary key is dropped. Unless, say, the dropped key definition matches exactly of what should be created. Then an error would be less confusing than auto-creation (otherwise one could try to drop ref_a key, got a success back, but the key wouldn't be deleted).