What is Primary Key and Foreign Key

 

Primary Keys ensures that records in the table are unique.

When you create a primary key on a column, a value entered into that column can not be re-entered for the next future insert.

For example, you put a primary key in the ID column and set the value of 1 for your first insert.

You can not set 1 again in your second insert. A null value can not be entered in a column with a Primary Key.

If you have a column that provides a unique value, you can put a primary key on that column.

Or you can provide uniqueity by putting Identitiy. In a table we can define only one Primary Key.

However, by combining multiple columns, the Primary Key can be defined. This is called the Composite Key.

Another feature of Primary Keys is to match with Foreing Key.

For example, in the City Table you put a primary key in the column named ID. The other column name is CityName.

Let’s imagine that there is another table called CityFeatures.

In this table we also have a column named CityID and some features of the City.

When we set the ID column in the City table as Primary Key, the CityID in the CityFeatures table as the ForeignKey column.

If you do not delete all records belonging to the relevant city in the City Properties table, you can not delete the record of the relevant city in the city table.

This way we will protect our data integrity. Implement our example practically.

Let’s create the City and CityFeatures tables with 2 scripts below.

Create the City table with the following script.

 

With the script below, let’s create the table of CityFeatures.

 

With the following script, let’s define a foreing key for the CityID column in the table of CityFeatures, whose primary column is the ID column in the City table.

As you can see in the Script, we put ForeingKey in the CityID column in the table of CityFeatures, and as a reference, it will base on the ID column in the city table.

 

Add a record to the city table with the following script. As you can see, we just entered value for CityName column.

Because we put an increasing identity in the ID column. So when each record is added, the ID column will increase automatically.

 

Let’s try to add a record to CityFeatures as follows.

 

When we run the above script, we will get an error like below.

The Insert statement conflicted with the FOREIGN KEY constraint FK_CityFeatures_City. The conflict occured in database “Test”, table “dbo.City”, column ‘ID’.

The reason for the mistake is that there is no city with 2 IDs in the city table.

When we run the script as follows, we will see that the record has been added.

 

When we try to delete the city table with the following script, we will get an error like below.

 

Could not drop object ‘dbo.City’ because it is referenced by a FOREIGN KEY constraint.

In order to delete the city table, we first need to delete the foreign key in the table CityFeatures.

Now, with the help of the following script, let’s try to delete the line with ID 1 in the city table.

 

The script will fail as below because there is no record with CityID value 1 in the CityFeatures table.

The DELETE statement conflicted with the REFERENCE constraint “FK_CityFeatures_City”.

If we first delete the values ​​with ID 1 in the CityFeatures as follows and then run the above script again, a successful deletion will occur.

 

As you can see in the example, we can guarantee our data integrity with many such similar relations using Primary Key and Foreign Key.

You may want to read my article named “Differences Between Primary Key and Unique Constraint