Chapter 1. Database overview, primary key, foreign key

Database:

Warehouse where data is stored, data is classified by table

Features: mass storage, fast searching, control of concurrency problems, security, data integrity (the data stored in the database is correct and true)

database software: DBSM Database Management System

Common database software: MySQL MSSQL Server Oracle Access

SQL: Structured Query Language Structured Query Language features: Simple language, easy to learn and easy to use.

SQL Server: is a large network-based database software. The main use of SQL language to manage, modify the database

The file composition of the database:

. mdf main File

. ndf Secondary File

. ldf log File

PRIMARY Key PRIMARY Key

role: uniquely identifies a record in a table

Features: cannot be duplicated; cannot be empty (null)

Classification:

(different from stored information) business primary key; Logical primary key

Single-column primary key (different number of columns), multi-column primary key (combined primary key, federated primary key, composite primary key)

Choose:

A column that is not empty;

Columns that do not relate to the actual business (logical primary key);

Stable columns (the data stored in the column does not change frequently);

preferably a single row;

Try choosing a numeric type as the primary key

Attention:

1. There can only be one primary key in a table, and when the primary key is established, the index is indexed by default, and the actual data is stored on disk in the same order as the primary key column, because the actual physical order can only have one primary key in a table.

2. After creating a table, you can not set the primary key, but it is recommended that each table has a primary key.

FOREIGN Key FOREIGN Key

Problems with data redundancy:

1, resulting in the waste of storage space

2. Update exception, delete exception

Troubleshooting Data redundancy:

The data in the original table is split into multiple tables to be stored. Use primary keys and foreign keys to correlate data from multiple tables.

FOREIGN key:

Each table has its own primary key when the data is split into two tables for storage. In one table, in addition to the primary key, another column, which is used to refer to the other table of the primary key, then this column is the first table of the "Foreign key column", a table with a foreign key column is called "Foreign key Table", relative to the primary key is referred to the table is called the "primary key table."

Because the foreign key column refers to the data in the primary key column of another table, the data in the foreign key column cannot be arbitrarily written, and it must be data that already exists in the primary key column in the other table.

Chapter 1. Database overview, primary key, foreign key