Designing Database : Database Keys

Database에서 데이터의 효율적인 정의와, 각 테이블간의 관계 설정에 사용되는
Key 키워드 사용에 대해 정리하였습니다.

 

 

---

< Introduction >

Will learn how to designate certain columns of a database table as keys.

What are keys? A database key is a column or group of columns in a table that uniquely identifies a row in a table.

 

< Why do we need keys? >
Keys enable a database designer to place constraints on the data in a table. We want to enforce data integrity in our tables so that we avoid duplicity of information and strictly maintain relationships between tables. For example, a primary key will ensure that each row in a table is unique.

+. There are many types of keys: Super, Candidate, Primary, Foreign, Composite, and Secondary. 

 

 

---

< Primary Key >

A primary key is a designation that applies to a column or multiple columns of a table that uniquely identifies each row in the table. For example, a Social Security Number for an employee may serve as a primary key in an employee table with rows of employee data.

Designating a primary key on a particular column in a table ensures that this column data is always unique and not null. For example, there may be multiple recipes of the same name, each with its own id but no two recipes should share the same id.

 

Example)

ALTER TABLE attendees
ADD PRIMARY KEY (id); 

CREATE TABLE attendees (
  id integer PRIMARY KEY,
  name varchar NOT NULL,
  ...
)

 

 

---

< Key Validation >

How to validate the keys that you have designated to specific column(s) in a database table?
There are several ways to do so, however, we will focus on utilizing the information schema database that comes with PostgreSQL.

 

< Information Schema >
the information schema is a database containing meta information about objects in the database including tables, columns and constraints.
This schema provides users with read-only views of many topics of interest.

The Information Schema

 

Example : Suppose you would like to find out the constraints that have been placed on certain columns in a table, such as recipe

SELECT
  constraint_name, table_name, column_name
FROM
  information_schema.key_column_usage
WHERE
  table_name = 'recipe';
  
 -- result
 constraint_name | table_name | column_name 
 ----------------+------------+-------------
 recipe_pkey     | recipe     | id
 (1 row)

: to determine if a column has been designated correctly as a primary key, query a special view, key_column_usage, generated from this database.

 

 

---

< Composite Primary Key >

When none of the columns in a table uniquely identify a record, we can designate multiple columns in a table to serve as the primary key. (a.k.a. composite primary key)

 

< Syntax >

PRIMARY KEY (column_one, column_two)

To designate multiple columns as a composite primary key, use this syntax

 

Example : we have a popular_books table that have these columns:

• book_title,
• author_name,
• number_sold
• number_previewed

Since an author can have many books and a book can have many authors, there could be repeated listings of a particular book or author in the table.

-- a listing of popular_books sorted by book
      book_title      | author_name | number_sold | number_previewed 
----------------------+-------------+-------------+------------------
 Postgres Made Easy   | Liz Key     |          33 |               50
 Postgres Made Easy   | Tom Index   |          33 |               50
 Beginner Postgres    | Tom Index   |          55 |               75
 Postgres for Dummies | Liz Key     |          25 |               33

: the book title Postgres Made Easy is listed twice since it has two authors.

-- a listing of popular_books sorted by author name
 author_name |      book_title      
-------------+----------------------
 Liz Key     | Postgres Made Easy
 Liz Key     | Postgres for Dummies
 Tom Index   | Postgres Made Easy
 Tom Index   | Beginner Postgres

: an author appear twice.

 

=> As we see from above, neither book_title nor author_name can be a unique column.

However, A composite primary key can be derived from the combination of both book_title and author_name that would make a row unique.

CREATE TABLE popular_books (
  book_title varchar(100),
  author_name varchar(50), 
  number_sold integer,
  number_previewed integer,
  PRIMARY KEY (book_title, author_name)
);

 

 

---

< Foreign Key >

To maintain data integrity and ensure that we can join tables together correctly, use Foreign Key.

A foreign key is a key that references a column in another table.

 

Example : Suppose we have a person table and an email table.

< Person, Email table >

So, Where do we place this foreign key? Should it be in the person table or email table?

Does creating a person record require that an email record exists as well? This is not usually the case. A person can have no email address or one or more email addresses.

Does creating an email record require that a valid person record exists? This is usually the case, since we shouldn’t create an email address for a non-existent person.

Hence, we should place the foreign key in the email table

CREATE TABLE person (
  id integer PRIMARY KEY,
  name varchar(20),
  age integer
);
 
CREATE TABLE email (
  email varchar(20) PRIMARY KEY,
  person_id integer REFERENCES person(id),
  storage integer,
  price money
);

 

+. Now that you have related two tables together via a foreign key, you have ensured that you can correctly join the tables back together in a query.

-- 1
SELECT person.name AS name, email.email AS email
FROM person, email
WHERE person.id = email.person_id;

-- 2(same)
SELECT person.name AS name, email.email AS email
FROM person
JOIN email
ON person.id = email.person_id;