How are constraints stored internally in computers?

Question

Practicing SQL I've came across with an error:

 ALTER TABLE articulos DROP CONSTRAINT fk_idCliente Error Code: 3940. Constraint 'fk_idCliente' does not exist.

Just to give context, I was trying to drop the foreign key of this table:

 CREATE TABLE `articulos` (
  `IdArticulos` int NOT NULL,
  `Nombre` varchar(50) DEFAULT NULL,
  `fk_idCliente` int DEFAULT NULL,
  PRIMARY KEY (`IdArticulos`),
  KEY `ARTICULOS` (`fk_idCliente`),
  CONSTRAINT `ARTICULOS` FOREIGN KEY (`fk_idCliente`) REFERENCES `clientes` (`IdCliente`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

I was using this code:

ALTER TABLE articulos
DROP CONSTRAINT fk_idCliente;

Because I though:

In " CONSTRAINT `ARTICULOS` FOREIGN KEY (`fk_idCliente`) REFERENCES `clientes` (`IdCliente`) ON DELETE RESTRICT ON UPDATE RESTRICT" what goes after "Constraint" had to be the same name as its table.
Therefore I supposed that in ALTER TABLE articulos DROP CONSTRAINT fk_idCliente; what goes after "Constraint" had to the name of the column that is foreign key.

However I was wrong, I don't know why, but it appears that what goes after " CONSTRAINT `ARTICULOS` FOREIGN KEY (`fk_idCliente`) REFERENCES `clientes` (`IdCliente`) ON DELETE RESTRICT ON UPDATE RESTRICT", I mean, the word "Articulos" is a kind of a name. So I could drop foreign key using that name, like this: ALTER TABLE articulos DROP CONSTRAINT ARTICULOS; -- This "ARTICULOS" is the name of... the Constraint?

All said, my question is: Why do I have to give it a name? I mean, Is foreign key stored like a variable or something similar? Is it the same case for the rest of constraints like "Check"?:

ALTER TABLE articulos
ADD CONSTRAINT Constraint_Name CHECK (IdArticulos<100);

score 0 · Answer 1 · answered Jan 19 '23 at 12:50

What goes after CONSTRAINT is the name of the constraint. It can be any name, not the name of the table. Constraint names have to be unique, so if a table has multiple constraints, they have to have different names.

If you don't give a name when creating the constraint, MySQL will make up a name automatically. In the case of foreign key constraints, it derives the name from the columns. But that's not what happened when your constraint was defined.

This is the name you have to give when dropping the constraint:

ALTER TABLE articulos DROP CONSTRAINT ARTICULOS;

How are constraints stored internally in computers?

1 Answers1