Database Table Management

type

status

date

slug

summary

Introduction to Key Fields

The following presentation link is provided by Hex School

SQL

https://miro.com/app/board/uXjVLMcM_hQ=/

Primary Key ( PK )

Each database table must have a primary key.

The value of the primary key must be unique and cannot be duplicated.

The primary key cannot be null, but a foreign key can be null.

In most cases, it's named id and uses an integer or UUID.

Once set, it should not be modified.

Set to auto-increment ( SERIAL )

Constraint Reminder

❗

duplicate key value violates unique constraint “users_pkey”

Foreign Key ( FK )

Constraints are used only when data needs to relate to other tables.

Naming usually follows the format referenced_table_id.

A foreign key must correspond to the primary key of the referenced table.

When to Use a Foreign Key

📌

The "many" side should be set as the foreign key.

Process: Plan from the perspective of fields

From the employee's perspective: An employee belongs to one department.

From the department's perspective: A department has multiple employees.

Constraint Reminder

❗

insert or update on table “users” violates foreign key constraint “users_team_id_fkey”

Create Data Tables

Usage of Relating Multiple Tables

📌

INNER JOIN

Questions

Q1

To save the database of 明華國小, which field is suitable to become a foreign key?

Ans

The field 班級 is suitable to become a foreign key.

Q2

An extension of the first question: now there's an additional "Class Teacher.”

Ans

Q3

For a child’s family classification database, the repeated information about parents is really annoying!

Ans

Question from a Classmate

Update the teacher of students aged 8 to 廖洧杰, and retrieve the data of students, teachers, and classes.

The Question I Created for the Next Student

Please retrieve the student ID, name, and class teacher for 三年一班.