SQL Create Table With Primary Key | IF NOT EXISTS

Опубликовано: 05 Ноябрь 2024
на канале: The SQL Guy

638

In this SQL tutorial we are going to look at how to create a table with a Primary Key (PK) in SQL Server. We will also look at how to add a Primary Key to an existing table, also looking at how to create a primary key on a temp table.

Content

00:00 Intro
00:25 Create table with Primary Key
01:50 Create Primary Key on table
03:54 Create primary key on temp table

What does a Primary Key Do?

A Primary Key ensures uniqueness in a table. What does that mean? It means that each row in the table can be uniquely identified, in this case by a number. For example, if you work at a company, you will have an employee Id. No one else has this same ID as you, if they did the business would have all sorts of problems with payment and booking holiday etc.

Primary Keys are the backbone of good table design in SQL Server, when we design tables, we need to add a PK to ensure that we don’t accidently add an employee with the same ID.

So this is vital stuff we are learning here! It’s pivotal to your SQL journey.

Naming Primary Key on SQL Table

As explained in the lesson, we should be naming our PKs, this gives us a clear description of what it is (prefixed with PK) the table name it is part of and the column it is assigned to!

Its good practice to do this, we can also run a query (if we wanted) on sys.objects and filter all the PKs, this would help us easily identify which PKs we have on which table and the columns they are part of. It saves us manually going into each table and searching – and we all love a bit of time saving!

Adding Primary Keys to Temp Tables

The only time I suggest we don’t name Primary Keys would be on temp tables. Why? Temp tables are usually part of a stored procedure which means multiple people could run the same stored procedure at the same time.

SQL doesn’t allow duplicate object names and types, so we wouldn’t be able to create two Primary Keys with the same name. The second person running this SP would get a failure.

We can create multiple temp tables with the same name as that’s handled differently – but I will go over that in another lesson.

Adding Primary Keys to Existing Tables

Sometimes we come across and existing table which we may want to repurpose or someone forgot to add a PK on. In this case we can add the PK after table creation.

This is something we should all know. Sometimes it can be a bit tricky if the data in there is duplicated but that is also for another time! In this tutorial we cover how to ALTER a TABLE and add a Primary Key – with and without a name.

Hope this tutorial has helped.

Please like and subscribe