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'.

Comentários

Postagens mais visitadas deste blog

Article: Preventing Database Gridlock: Recognizing and Resolving Deadlock Scenarios

Learn how deadlocks occur in database systems, understand their impact on performance, and discover practical techniques for identifying potential deadlock scenarios in your SQL code.

Demystifying MySQL ALTER TABLE ALGORITHM: INPLACE vs INSTANT vs COPY

Schema evolution is one of the trickiest aspects of database management. For developers and DBAs, the dreaded ALTER TABLE command often conjures fears of downtime, locks, and performance bottlenecks. But MySQL offers a powerful word! The "ALGORITHM" modifier that determines how schema changes are executed. In this article, we’ll break down the three key algorithms "INPLACE", "INSTANT", and "COPY" to help you understand their trade-offs, performance implications, and even some unconventional ways to use them. Whether you’re optimizing for zero downtime or experimenting with stress testing, this guide will give you practical insights and real-world strategies. IMPORTANT: Check your MySQL version: In MySQL 8.0+, many table changes are instantaneous, and the command modifications presented here are only available in this and later versions. Understanding the ALGORITHM Modifier When you run an ALTER TABLE in MySQL, you can specify the algorithm, lik...

NestJS Clustering: Advanced techniques to increase NestJS Apps response

In today’s fast-evolving digital landscape, performance and scalability have become the lifeblood of modern applications. As advanced JavaScript developers, we continuously seek strategies to optimize our code and leverage every ounce of our infrastructure. NestJS clustering promises not just incremental improvements but exponential enhancements by fully unlocking the potential of your virtual machines. This article delves into NodeJS clustering within the NestJS framework, exploring its theoretical underpinnings, offering practical, code-rich examples, and even highlighting an experimental twist that can set your application apart. Unlocking the Power of NodeJS Clustering At its core, NodeJS clustering is about taking full advantage of multi-core systems. Typical NodeJS apps run as single-threaded processes, which can be a bottleneck when you need to handle a massive number of concurrent operations. Clustering allows you to spin up multiple worker processes that share the same server ...

rstriquer

Pesquisar este blog