Hey everybody, in this topic I'm going to explain the primary key constraint in MySQL. The primary key constraint can be applied to a column where each value in that column must both be unique and not null. It's typically used as a unique identifier. For example, I live in the United States. Each citizen within the United States has a unique social security number.
There is a strong possibility that two citizens in the United States share the same first name and last name. John Smith, for example. If we're trying to find John Smith, well, which one are we referring to?
We could instead search for a citizen with a unique social security number. Then we know for sure we have the right person. That's kind of the same concept with the primary key. Also, a table can only have one primary key constraint. Here's an example.
Let's create a temporary table of transactions. Create, Table, Transactions. There will only be two columns. a transaction id. The data type is int.
I will set the transaction id to be the primary key. There can be no duplicate transaction ids with the same value and none of them can be null. Then we will also have an amount.
How much was each transaction for? The data type will be decimal. Max size of five digits, precision of two, and that is good enough.
Then I will select all from our table transactions. Here is our table. Now to add a primary key constraint to a table that already exists, this is the syntax.
Alter table, the name of your table, add constraint, the name of the constraint, primary key. Within parentheses, the column we would like to apply the primary key to. Transaction id in this example, then semicolon.
However, we have already applied the primary key constraint to our transaction id column, so this isn't going to work. But that's how you would add a primary key constraint to a table that already exists. Let's test that theory of that limit of one primary key per table. I'll attempt to add a primary key to our amount column. And it does not appear we can do so.
Multiple primary key defined. So like I said, you can only have one primary key per table, and that is typically used as the identifier. Let's take a look at our transactions.
Let's populate this table with a few rows. We will insert into our table transactions some values. All we need is a transaction id and an amount.
For our transaction ID, I'll just make up a number. All transactions will start in the thousands. This will be our first transaction. So the amount, a customer bought a hamburger and a soda, the total was $4.99. So let's run this.
There is our first row. For the second transaction, the transaction ID will be 1001. The customer bought fries and a soda for $2.89. So that appears to have worked as well.
Now the next customer buys fries and ice cream for $3.38. Let's test that theory of each transaction id having to be unique. I'll attempt to insert this row with the same transaction id as the previous row. Then let's see what happens.
Duplicate entry 1001 for key transactions.primary. Yeah, it appears that we cannot have two rows with the same primary key. They each need to be unique.
Let's change the transaction ID to 1002. That appears to have worked. Now the next transaction will be a hamburger and soda for $4.49. I will attempt to set the transaction ID to be null. which we can't do either. Column transaction ID cannot be null.
Each value that's set as a primary key can't be null and it needs to be unique. So this transaction will have a transaction ID of 1003. Two rows can share similar data. We have two customers that bought both a hamburger and a soda for the same price.
But each transaction is uniquely identified by the transaction ID, which we set as the primary key. Let's say that somebody comes in to request a refund and they give us a transaction ID. Well, we can easily look that up and refund them whatever the amount was.
Select amount from transactions where transaction ID equals 1003. So we are going to refund the customer $4.99. Well, alright then everybody, that is the primary key constraint. It's most commonly used as a unique identifier.
You usually want a primary key in every table you make. Each value within a column that is set as the primary key cannot be null, and it needs to be unique. There can only be one primary key per table. Typically, it's used as a unique identifier for each row.
And yeah everybody, that is the primary key constraint in MySQL.