SQL: Primary Key constraint name convention

Demand

ALWAYS name forein keys constraints as "fk_{LocalTableName}_{LocalColumnName}_{DestinationTableName}_{DestinationFieldName}"

Description

We use this convention to easily identify the source of the failure, especially in schema updates.

When creating a CONSTRAINT we create its name starting with the string "fk", followed by the full name of the source table, also called local table or child table, followed by the name of the column used in the source table to store the value to be searched for later in the target table, the name of the target table and finally the name of the field in the target table. All separated by "_" (underline).

Examples

1:  CREATE TABLE customers (
2:      id INT NOT NULL,
3:      name VARCHAR(100) NOT NULL,
4:      user_id INT NULL COMMENT 'if the customer have a system login it will be refernced here',
5:      PRIMARY KEY (id),
6:      CONSTRAINT `fk_customers_user_id_users_user_id`
7:         FOREIGN KEY (`user_id`)
8:         REFERENCES `users` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
9:  );  

Examples Explanation

Sample of an error output using MariaDB

1: SQLSTATE[HY000]: General error: 1833 Cannot change column 'id': used in a foreign key constraint 'fk_customers_user_id_users_user_id' of table 'mydb.customers'

Sample of an error output using MS-SQLServer

1: The UPDATE statement conflicted with the REFERENCE constraint "fk_customers_user_id_users_user_id". The conflict occurred in database "mydb", table "mydb.customers", column 'user_id'.