12/5/2023 0 Comments Sql pound keyDatabases operate more quickly with numbers than strings. So, in this case, we will reserve 10 positions for the letters and numbers in the flight number.Īlthough non-numeric primary keys are technically feasible, numeric primary keys tend to perform better. Usually, flight numbers are a combination of letters and numbers of a fixed length. The property_id column can be the primary key.īecause property_id is a combination of letters and numbers, we use a char(11) data type to store it.Īnother example of a non-numeric primary key is a table of flights of an airline company. Each property is identified by a two-letter property ID code followed by an eight-digit number, like BV-11234134. Note that we use a char(3) data type for the airport_code to store the three-letter code.Īnother example is a table with all of the registered properties in a specific state. The following SQL command creates the table airport with a primary key in the column airport_code: We can use this non-numeric code as the primary key. The airport code is a unique three-letter code assigned to each airport by an international aviation organization. Some of the columns are airport_name, airport_city, airport_country, and airport_code. However, it is possible to have primary keys of other data types.įor example, let’s suppose we have a table that stores all the airports in the world. In previous examples, we showed only numeric primary keys. If you have a primary key, you can be sure you will not have duplicates and NULL values in the primary key column.īefore continuing to the next section, I would like to suggest the article How to Create a Table in SQL where you can learn about data types and NULL constraints among other important concepts. However, database modeling best practices suggest creating a primary key for every table in the database. You can create an SQL table without defining a primary key. Is it mandatory to have a primary key in each table? No, it is not. INSERT INTO citizen VALUES (721071426, 'Kant','Peter','') ĮRROR: duplicated key violation for primary key citizen_pkeyĭETAIL: Already existing key (social_security_number)=(721071426).Īt this point, I want to share a question that I have heard several times. After defining the primary key, if we try to insert a new record with a duplicate value in the social_security_number column, the INSERT will fail with the following error: We used the PRIMARY KEY clause in the CREATE TABLE statement to define which column is the primary key. Social_security_number integer PRIMARY KEY, If we want to define a primary key for the table citizen, we can create the table in the following way: Now, let’s talk about the SQL syntax for creating a primary key. Suppose we want to create the table citizen as seen below: social_security_number In other words, primary keys guarantee all rows in the table have unique and non-null values in the primary key column. In the case of the table citizen, a row with a NULL value in the social_security_number column will be rejected by the primary key. NULL values are not allowed in the primary key column. For example, if you try to insert a new row with a duplicate value in the primary key column, this row will be rejected by the primary key.Īnother feature of primary keys is a control to avoid NULL values. Primary keys implement a technical mechanism to check if every row has a unique, non-empty value in the primary key column. You can view the primary key as the “main identifier” for every row in the table based on a certain column. Once you identify a column that has a different value for each row in the table, you can create a primary key using this column.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |