|
Custom Search
| |
|
Primary Key Constraint and Unique Constraint Primary Key Constraint is defined by creating a primary key on a table. The value in primary key must uniquely identify each row in the table. Primary Key Constraint enforces row integrity. Consider the following facts when defining a primary key:
What is artificial primary key? There are two types of primary key: artificial primary key and natural primary key. Artificial primary key is an integer number that is system auto-generated, auto-incremented, and maintained by MySQL database engine. It is the preferred way of defining and creating primary key. Artificial primary key is also known as surrogate primary key. In MySQL, when we define a primary key as auto_increment, it will automatically increment by 1 every time when we add a new record into the table. This number is just an integer number and have no meanings whatsoever - you can call it stupid key.
Defining primary key:
Data in primary key column CategoryID: What is natural primary key? As its name suggests, natural primary key takes the natural format of the data and often has business meanings associated with it. Natural primary key is also known as intelligent primary key. For example, primary key CustomerID in customers table is a natural primary key. It uses a string of 5 characters which is abbreviated from the customer's company name.
Natural primary key: Why we should use an artificial primary key From the example showing above for CustomerID, the drawback of using natural primary key is obvious. First, the primary key is created by shortening the company name. It's rather cumbersome to come up with a unique short name for a customer. Second, it's not flexible and is subject to business requirement changes.
To optimize the Customers table, we can create a new integer type CustomerID column in customers table as artificial primary key and remove the previous varchar type CustomerID. We then renamed the old varchar type CustomerID column to CustomerAbbr (if we still want to keep this column) which is short for customer abbreviation. We also added unique index to CustomerAbbr column.
Artificial primary key: Dis-advantages of using artificial primary key: The only disadvantage I can think of is when we retrieve data from tables by using joins, we'll have to join more tables than we need. The extra joins come from the lack of natural key in the foreign key tables. The following two sets of screenshots illustrate how extra joins can occur. Table 1: Use extra join to get CustomerID
Table 2: No join is used to get CustomerAbbr
The queries in Table 1 and Table 2 return exactly the same result but Table 1 used JOIN but Table 2 didn't. Unique Constraint Unique Constraint defines that values in a column must be unique. No duplicate values are allowed in the column. Unique Constraint enforces row integrity. Unique Constraint is created on a column when you want to guarantee that data in this column must be unique. For example, a unique constraint is created on CategoryName column in categories table. This is achieved by creating a unique index on CategoryName column.
Considerations when creating unique constraint:
When on multiple columns, the combination of data on these columns must be unique. For example, we created a unique index on OrderID and ProductID column in order_details table.
On next page, we will look into details of Creating foreign key relationships and considerations. Other tutorials in this category 1. How to Design Relational Database |
Copyright © 2024 GeeksEngine.com. All Rights Reserved. This website is hosted by HostGator. No portion may be reproduced without my written permission. Software and hardware names mentioned on this site are registered trademarks of their respective companies. Should any right be infringed, it is totally unintentional. Drop me an email and I will promptly and gladly rectify it. |
Home | Feedback | Terms of Use | Privacy Policy |