Foreign key constraints for Database Design

When designing a database, we often have some constraints defined at the database level to ensure the data integrity of the database. These constraints include check constraints, non-empty constraints, unique constraints, primary key constraints, foreign key constraints, and so on. The preceding constraints are easy to understand and use. However, foreign key constraints are not difficult to understand, but they are not easy to use.

Next we will briefly review the knowledge of the above constraints.

 

Check constraints:

That is to say, when defining a field, define a value range. When an insert or update occurs, the database automatically checks whether the value is within the correct range. For example:

Crate table student (
Id serial,
Name varchar (10 ),
Scrore integer CHECK (scrore> 0)

);

 

Non-empty Constraint

A non-Null constraint defines that a field cannot have a Null value. It is usually Not Null. If it is only a non-empty constraint, data duplication is allowed.

 

Unique Constraint

UNIQUE constraint. Use UNIQUE. Indicates the data in the field, which is unique in the table. Data with unique constraints can have NULL values.

 

Primary key constraint

The primary key constraint is the union of non-empty constraints and primary key constraints. The primary key value can be a string sequence generated by a unique encoding algorithm.

 

Foreign key constraint

Foreign key constraints are a common data constraint. However, it is not that simple to use. Defines foreign key constraints. The foreign key can be used for single or multiple columns. Foreign key and REFERNCES are often used together. References indicates the column of the table referenced by the column as the foreign key. The foreign key can be omitted in a single column.

Example: job_id smallint not null default 1 REFERENCES jobs (job_id)
The preceding definition can also be written as follows:

Job_id smallint not null default 1 foreign key (job_id) REFERENCES jobs (job_id)
The foreign key job_id references the job_id field of the jobs table as the foreign key.
A problem caused by foreign key constraints is how to deal with the data in another table when you try to delete or update the foreign key. This involves cascade update and cascade deletion.

In the database specification, cascade update and cascade deletion methods are also defined.

The references clauses of create table and alter table support the on Delete and on update clauses. If this parameter is not specified, the default value is no action, that is, on Delete no action, and on update no action. When a delete or update operation occurs, the transaction is rolled back and no related operations are performed.

The cascade clause allows operations by the on Delete casecade and on update cascade clauses.

If it is required to be updated and cannot be deleted during definition, write as follows:

Create Table score (
Id integer

References student

On update Cascade

On Delete cascade,
Class_id varchar (5)

References class

On update Cascade

);

The combination of cascade and no action can make the foreign key constraints take full effect without causing confusion caused by system data constraints.