Establish the table's primary key

- [Instructor] There are several ways to establish a table's primary key using SQL commands. I currently have the file called PrimaryKey-SQLServer from the chapter three folder here opened up on my Azure Data Studio instance. If you're following along, make sure that you have the TwoTrees database in the SQL Server container selected before you open this file. And then double-check that the connection type says TwoTrees here in this dropdown menu. Now, the first way to establish a primary key is to identify it right when you create the column in the table. Here, starting on line number four, I'm creating a new table called customers. It has a number of different columns. And the first one, the customer ID, will be the primary key for this table. A table can only have one primary key. It must be a unique value within the table, and it cannot be left blank or null when saving a record. Because of this, it's referred to as a constraint, since it places restrictions on the data that can be entered into this column. I'm also adding a not-null constraint to this column. We'll talk more about what this means later on in the course, but it basically just means that we can't enter in a null value or an empty space when we enter in a row into the table. So this column is called customer_id. It'll use the char5 data type. It has a not-null constraint. And it'll be the primary key for the table. Let's run lines four through 11 to create this table on the server. Now we can take a look at it over here in the connection sidebar in Azure Data Studio. I'm going to right-click on the TwoTrees database and choose refresh. Then we'll expand the tables folder, and we'll find the new customers table right there. We didn't specify any special schema, so it's just going into the DBO schema, which is the default for a SQL Server. So inside of the customer's table, I can expand the columns, and we'll see all those columns right there. The customer_id is identified as the primary key here in parentheses with this PK notation. We can also take a look inside of the keys folder, and we'll see our primary key right there. Because we didn't specify otherwise, the key got a name with this unique serial number appended to it. Now, the exact same commands that we just ran will also work on PostgreSQL in order to create the table and the primary key. But the display here in Azure Data Studio will be a little bit different. The PostgreSQL plugin that we're using is still in a beta-testing stage. So it's not quite complete, and it won't display the proper constraint and key information in the sidebar. But the commands will still work, and it'll still configure the table with the primary key. So this is the first way to add in a primary key column to your tables. You just specify primary key right when you name the column. The second way is to add the primary key constraint at the end of the create-table statement. So let's take a look at that. I'm going to move this messages window down, and I'm going to run line number 13 that says drop table. We're going to recreate it starting on line 18. This method goes through the steps of creating all the different columns like we've done before. We have customer_id to char5, and it has the not-null constraint. Then I list out all the different columns there. At the end of the whole thing, I make sure that I type in a comma here at the last column. And then we have the key word, constraint. This method allows us to name the constraint rather than getting an auto-assigned generic name for it. Here, I'm naming the constraint PK_Customers_CustomerID. This will help me identify it as the primary key constraint. It's on the customer's table and the customer ID column. Then we have the key words, primary key. And in parentheses, we specify which column will be the primary key. In this case, it's the customer_id column. Naming the primary constraint with this method will make it easier to recognize in the sidebar and in code if I decide to work with it later. So if I run lines 18 down to 26, that's going to create the exact same table. And if I come over here into the sidebar and refresh my database and take a look inside of the tables folder and the customer's table, I can now go into the keys folder, and I'll find the named primary key right there. You can see that the name makes it a lot easier to recognize. So that's the second method. Let's go ahead and run line number 28 to drop the table again. Then I'm going to scroll down and find line number 33. The third way to add in a primary key constraint is after the table is created. First, you'll create the table as normal with just the column names and the data types. So I can do that by running lines 33 down to 40. Then once the table is created, we can alter the table. I'm going to alter table customers, and then we can add in the constraint. I'll name the constraint the same way. It will be a primary key constraint, and it'll be on the customer_id column. Let's run lines 42 through 43 to add in the constraint to the table that we just created. Once again, I can refresh the database over here in the sidebar and take a look in the tables. Here is my customer's table. And if I expand the columns, you'll see the customer_id is still listed as the primary key. And if I go into the keys folder, you'll see the primary key listed right there. No matter which method of these you choose, the end result is the same. Your table is created, and there are some constraints put on the primary key column. Most importantly, it will now no longer allow duplicate values. Let's test that out. I'm going to scroll down here on my script and find line 47. First, we're going to insert a row into the customer's table for Flavorville. Flavorville will have a customer ID of FV418. I'll go ahead and run line 47 and 48 to insert that row into the table. All right, one row is affected. So that row has been entered into the database. Now let's try and enter in a second row. This one is going to be for a company called Wild Rose, but we're going to use the same company ID that we just inserted, FV418. If I try and run lines 50 and 51 with the duplicate ID value, you'll see the result. The server tells me that I have a violation of the primary key constraint. It names the constraint here, and it tells me that we have a duplicate key value. In order to fix this, we need to make sure that all of our primary keys are unique. So I'm going to change this value from FV418 to WR421. Now I can insert this row into the table without a violation of that constraint. I get the message that one row is affected again. And now if I select everything from the customer's table, we'll see that both rows have been successfully entered. So that's how you add a primary key to your data tables using SQL data definition language commands. You can either create the primary key with a new table or use the alter-table statement paired with add constraint.