If you’ve already worked with databases, then you could hardly miss the term – Primary Key (PK). And if you’re reading this series and use it to learn about databases, well, this article should give you a good overview of what the PK really is. Still, if you’re a database expert, maybe you’ll find something new or just refresh your knowledge. So, sit back, relax, and let’s dive into PKs. Show
What is a Primary Key (PK)?We’re literally surrounded by PKs in the database world. But we mostly take them for granted. Before examples let’s go with a simplified definition of a PK: “Primary key is a value which is unique for every record in the table.” And a rule – “Each table in the database should have a PK defined.”
Our database structureWe can expect that in our database we’ll almost always have 1 table for 1 entity from the real world. For example, we’ll have 1 table for countries, 1 for cities, 1 for employees, 1 for calls, etc. Now, we’re ready to go with examples of primary keys from real life:
We’ll conclude this part with a more descriptive definition. “Primary key is a value, or a combination of few values from the table, uniquely defining each record in this table. If we know this value/combination, we can easily find the related record and access all remaining values from that record.” For instance, if you know the name of the country, you’ll easily find the row with that name and have access to remaining values related to that country – population, statistical data, etc. What is the purpose of the Primary Key (PK)?Without PKs, databases wouldn’t work as they work now, or to be precise, wouldn’t function at all. Let’s take a look at what is in our database. To expand the “tree” click on the “+” sign next to “Databases”, database name (“our_first_database”), and table names (“dbo.city” and “dbo.country”) and “Keys”:
You can easily notice that both tables have PKs (“city_pk”, “country_pk”) and that the “dbo.country” table has alternate keys / unique values ( “country_ak_1”, “country_ak_2”, “country_ak_3”). The key “city_country” is the foreign key and we’ll discuss this later. In order to clarify where all of this came from, let’s remind ourselves of the commands used to create these two tables:
We’re using databases to store (usually) large amounts of data. In the previous article, we’ve inserted rows in our tables. That is cool, but there’s no point in adding rows if we can’t do anything with them. If we want to do that, we need a way how to recognize each separate row using known value(s). I would separate these values into two groups:
Still, there is some magic in the second approach because in some cases, the system knows what is the PK value. So, the purpose of the PK is to use it when we’re accessing data and when we (or system) know that value. We’ll explain how the system knows this value in the next article while describing foreign keys. Primary key VS. UNIQUE (alternate keys)When I’m designing a database, I always stick to a few rules regarding PKs and unique values. This is the best I’ve learned (so far) and besides technical reasons, it’s also worth mentioning that using this approach you’ll stay consistent throughout the whole database model. So, my rules are:
Since we’ve defined the UNIQUE rules on 3 columns, all 3 of them will prevent this Insert statement and this is the desired behavior. We’ve defined rules and the system takes care of whether the data we want to insert follow these rules or not. ConclusionUnderstanding what primary key is and when it’s used is the backbone of the database theory. The next thing is understanding the concept of a foreign key and how foreign keys are used to relate data. We’ll cover this in the upcoming article. With these two in your pocket, you have all that’s needed to start creating databases and to expend your knowledge along the way. There is a lot more that has to be learned in order to become a database professional, but you’ve just made your first steps towards that direction. Table of contents
Emil is a database professional with 10+ years of experience in everything related to databases. During the years, he worked in the IT and finance industry and now works as a freelancer. His past and present engagements vary from database design and coding to teaching, consulting, and writing about databases. Also not to forget, BI, creating algorithms, chess, philately, 2 dogs, 2 cats, 1 wife, 1 baby... You can find him on LinkedIn View all posts by Emil Drkusic What is primary key and unique key with examples?A primary key is a column of table which uniquely identifies each tuple (row) in that table. Primary key enforces integrity constraints to the table.
...
Comparison Chart:. What is an example of a primary key?A primary key is a column -- or a group of columns -- in a table that uniquely identifies the rows of data in that table. For example, in the table below, CustomerNo, which displays the ID number assigned to different customers, is the primary key.
What are the example of primary key in SQL?In SQL, the PRIMARY KEY constraint is used to uniquely identify rows. The PRIMARY KEY constraint is simply a combination of NOT NULL and UNIQUE constraints. Meaning, the column cannot contain duplicate as well as NULL values. Here, the college_id column is the PRIMARY KEY .
Which of the following is a primary key?Which one of the following attribute can be taken as a primary key? Explanation: The attributes name, street and department can repeat for some tuples. But the id attribute has to be unique. So it forms a primary key.
|