SQL: Unique Key constraint name convention

 

Demand

ALWAYS name unique key as "uq_{IndexName}"

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 "uq", followed by the full name of the source table column used to build the index. Both 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:      UNIQUE KEY `uq_user_id` (`user_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

Here the uq_user_id is used to build a unique key to the user_id column