Maintaining that list of values is an application setup task and requires the development team to build an entry and update form to input and maintain the lists. There are known lists that occur in most if not all business applications. The following small ERD displays a multi-language lookup table, which is preferable to a monolinquistic enum data type.:Ī design uses a lookup table when there are known lists of selections to make. Implement all surrogate primary key columns and foreign key columns with the signed int as their initial data type. While fixing a less optimal design is a relatively simple scripting exercise for most data engineers, you can avoid this maintenance task. Add back foreign key constraints after changing the referenced primary key and dependent foreign key column’s data types.Change the primary and foreign key column’s data types.Remove any foreign key constraints before changing the referenced primary key and dependent foreign key column’s data types.Assuming you’re design uses referential integrity constraints, implemented as a foreign keys, you will need to: The maintenance task will require changing the data type of all dependent foreign key columns before changing the primary key column’s data type. This is an important design consideration because using a unsigned int adds a maintenance task later. More or less, this is what I wrote in MySQL Workbench Data Modeling & Development as the primary product guide in 2013, and what you find in the MySQL Workbench Manual 8.1.10.2 Columns Tab section.Īctive tables grow quickly and using a signed int means you run out of rows more quickly. The UN checkbox ensure you have the maximum number of integers before you would migrate the table to a double precision number. The AI checkbox enables AUTO_INCREMENT behavior. You should also click the UN checkbox with the AI checkbox for all surrogate key columns. The default behavior checks only the PK and NN checkboxes and leaves the UN and AI boxes unchecked. The value 0 isn’t possible when you also select the PK and AI check boxes, which ensures the column automatically increments to the maximum value of the column.ĭesignates a zero fill populates zeros in front of any number data type until all space is consumed, which acts like a left pad function with zeros.ĭesignates AUTO_INCREMENT and should only be checked for a surrogate primary key value.Īll surrogate key columns should check the PK, NN, UN, and AI checkboxes. The possible values are 0 to the maximum number of the data type, like integer, float, or double. You can’t apply this constraint to other data types.ĭesignates a column contains an unsigned numeric data type. The column attribute table for MySQL Workbench is: Keyĭesignates a column contains a unique value for every row.ĭesignates a VARCHAR data type column so that its values are stored in a case-sensitive fashion. Then, you build a unique index for the natural key, which lets you query any unique row with human decipherable words. This means you elect, or choose, the surrogate candidate key as the primary key. While you can perform joins by using either candidate key, you always should use the surrogate key for joins statements. In a well designed table you always have two candidate keys: One describes the unique row and the other assigns a number to it. Surrogate keys are also candidate keys, like a VIN number uniquely identifies a vehicle. Surrogate keys are also unique in the table but should never be used to determine uniqueness like the natural key. In Object-Oriented Analysis and Design (OOAD), that makes the sequence a member of the table by composition rather than aggregation. While a number of databases disclose the name of sequences, MySQL treats the sequence as an attribute of the table. I explain that surrogate keys are driven by sequences in the database. Then, we discuss surrogate keys, which are typically ID column keys. I explain that a natural key is a compound candidate key (made up of two or more column values), and that it naturally defines uniqueness for each row in a table. Then, I need to remind them that every table requires a natural key from our prior discussion on normalization. As I teach students how to create tables in MySQL Workbench, it’s always important to review the meaning of the checkbox keys.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |