Pular para o conteúdo principal

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



Marcadores

Marcadores:

Comentários

Postar um comentário

Postagens mais visitadas deste blog

PHP: Always use an index to add a values to an array

  Demand Always identify the array index when manipulating the array directly Description Whenever you are adding a new item to the array you must add an index so that php does not need to recalculate the size of the array to identify in which position the new content will be added. Examples 1: # Bad code: 2: do { 3: $array[] = $newItem; 4: } while(!feof($fp)); 5: 6: # Good code 7: $array = []; 8: $index = 0; 9: do { 10: $array[$index++] = $newItem; 11: } while(!feof($fp)); Examples Explanation When instantiating a new item in an array in the "bad example" php will identify that you expect the item to be added to the end of the array, since array is a primitive type and has no attributes php always needs to call a calculation function to identify the current size of the array before trying to add a new item to the end of it. By monitoring the growth of the array in an external variable and automatically reference which position will...
Postar um comentário
Leia mais

General: Prevent arbitrary precision arithmetic

Demand Some simple decimal calculations like 4.6 * 100 may lead in irregularities when dealing with numbers. Description During arithmetic operations like addition, subtraction, multiplication, and division, the numbers are manipulated according to the rules defined by the IEEE 754 standard. However, due to the finite precision of the representation, rounding errors may occur, leading to small discrepancies between the expected and actual results of computations.  To prevent it we may use some libraries to prevent the problem. In Javascript and Typescript we have the BigNumbers library (and others). PHP has a built-in library for arbitrary-precision arithmetic called BCMath (Binary Calculator). Wich I reccomend. It provides functions to perform mathematical operations on numbers with arbitrary precision, including addition, subtraction, multiplication, division, and more. Examples PHP Sample 1: php > var_dump(floor((10*0.91597) * 1000000)/1000000); 2: float(9.159699) 3: php ...
Postar um comentário
Leia mais

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...
Postar um comentário
Leia mais