Mastering SQL: Part 1: Primary and Foreign Keys

Learning SQL

Using SQL

Mastering SQL

Structured Query Language (SQL) is a declarative programming language used for accessing data storied in relational databases. It appears in or is a core part of many common software packages like MySQL, PostgreSQL, Microsoft SQL Server, and Oracle Database.


Primary Keys

SQL is used for accessing data in relational databases. Rows are related to their column and to their larger databases. However, as relationships can change as the data is updated and changed, there needs to be a single, very rarely (if ever) changed relationship.

In nearly all implementations of SQL, there exists this single relationship between a row and its databases. This is known as the primary key. Often, although not always, this a number such as an ID or other form of identification.

A primary key MUST be unique in a relational database table. This means data can always be accessed through this “key” because it will always refer to an individual row. Even when column names or other data is changed, this primary key will remain.

CREATE TABLE People (
        ID int NOT NULL,
        Name varchar(255) NOT NULL,
        PRIMARY KEY (ID)
);

When using the keywords CREATE TABLE, most SQL implementations ask for a column to be identified as the primary key. This must be a NOT NULL (cannot be empty) column because a primary key cannot be NULL.

Table Name: People
IDName
1Dan
2Steve

Once created, primary keys are frequently used a part of a SELECT statement to get data based on this unchanging relationship.

Foreign Keys

Just as primary keys are used to create a relationship between a row and its table, a foreign key is used to link tables together. In relational database terminology, a foreign key is a column in one table that is a primary key in another.

Such a relationship creates a link between these tables that cannot easily be broken. Because of the foreign key connection, data can more easily be pulled from multiple tables based on a single column name.

Table Name: People
IDName
1Dan
2Steve
Table Name: Orders
OrderNumberID
45671
89012

Foreign keys are created using the CREATE TABLE keywords like primary keys.

CREATE TABLE Orders (
     ID int NOT NULL,
     OrderNumber int NOT NULL,
     PRIMARY KEY (ID),
     FOREIGN KEY (ID) REFERENCES People(ID)
);

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.