Foreign Key Learning

I have been developing an information equipment application management system over the past two days. When designing a database, I encountered a tangle problem, that is, the definition of foreign keys, because I don't know which fields need to be defined as foreign keys. Under what circumstances can I define foreign keys and how can I define foreign keys,

Today, I got a study written by Yang zhongke, who I bought earlier, and finally solved my confusion, so we can share the foreign key that I understand.

1. Foreign key definition: it is called "Table Association" when two database tables are joined by fields. The field associated with the primary key of other tables is called"Foreign key".

Ii. Foreign key definition principles: one sentence,When the value of a field appears repeatedly in the tableWe need to consider extracting them to another table and then referencing the data in the newly created table in the source table.

3. Foreign key definition method: foreign key (foreign key field name) References target table name (associated field name)

Example: Create Table t_employee (FID int, fnumber varchar (50), fname varchar (50), f1_mentid varchar (50 ),Foreign key (fdepartmentid) References t_department (FID))

4. If no foreign key constraint is added during table creation, you can use the alter table statement to add a foreign key constraint. The syntax is as follows:

Alter table table name Add constraint foreign key name foreign key (foreign key field name) References target table name (associated field name)

For example, alter table t_book add constraint fk_book_author foreign key (fauthorid) References t_author (FID );

Tip: Some conventions (or good habits) about database operations and SQL statement writing can avoid conflicts with system keywords and identifiers, and increase program correctness, it is also conducive to maintenance.

1. The database name starts with "DB.

2. The data table name starts with "T.

3. The field name starts with "F.

DB (database), T (table), F (field)