Menu
I'm trying to figure out the "best practices" for deciding whether or not to add an auto-incrementing integer as the primary key to a table.
Let's say I have a table containing data about the chemical elements. The atomic number of each element is unique and will never change. So rather than using an auto-incrementing integer for each column, it would probably make more sense to just use the atomic number, correct?
Would the same be true if I had a table of books? Should I use the ISBN or an auto-incrementing integer for the primary key? Or a table of employees containing each person's SSN?
595
You don't have to specify NOT NULL on the column definition with AUTO_INCREMENT . You can leave it off, and MySQL will make the column NOT NULL . And if you specify NULL in place of NOT NULL , MySQL will accept the syntax, but it will ignore that, and make the column NOT NULL anyway.
No, it is not required for every table to have a primary key. Whether or not a table should have a primary key is based on requirements of your database. Even though this is allowed it is bad practice because it allows for one to add duplicate rows further preventing the unique identification of rows.
By default, the AUTO_INCREMENT column begins at 1. You can also explicitly assign 0 to the column to generate the next sequence number unless the NO_AUTO_VALUE_ON_ZERO SQL mode is enabled.
So we can use Auto Increment feature that automatically generates a numerical Primary key value for every new record inserted. The Auto Increment feature is supported by all the Databases we are going to discuss the auto increment field for the subsequent DBMS: SQL Server. MySQL.
There are a lot of already addressed questions on Stack Overflow that can help you with your questions. See here, here, here and here.
The term you should be looking for: surrogated keys.
Hope it helps.
104
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
