Database basics: Constraints in databases and three paradigms

I. Paradigm in the database:

Paradigm, the English name is the Normal Form, it is the British e.f.codd (relational database ancestor) in the 70 's to put forward a relational database model, the paradigm is the basis of relational database theory, but also in the process of designing database structure to follow the rules and guidance method, The following is a basic introduction to these three paradigms:

First normal form (1NF):

Each column (field) in the datasheet must be the smallest unit that cannot be split, that is, to ensure the atomicity of each column.

For example: UserInfo: ' Shandong province Yantai 1318162008 ' according to the first paradigm must be split into
UserInfo: ' Yantai of Shandong Province ' Usertel: ' 1318162008 ' two fields

Second Normal form (2NF):

All columns in a table that meet the requirements of 1NF must depend on the primary key, and no one column has nothing to do with the primary key (a table describes only one thing).

For example, the order table can only describe information about the order, so all fields must be related to the order ID.
Product tables can only describe product-related information, so all fields must be related to the product ID.
Therefore, the order information and product information cannot appear in the same table.

Third paradigm (3NF): Third paradigm (3NF): After 2NF is met, the requirement is that each column in the table is directly related to the primary key, not indirectly (each column in the table can only depend on the primary key)

For example: The order form needs to have customer-related information, after the separation of the Customer table, the order form only need to have one user
ID, but not other customer information, because other user information is directly associated with the user ID, not the associated
to the order ID.

Precautions:

1. The essential difference between the second and third paradigms is that there are no two tables.

The second paradigm is to say that a table contains a number of different entities of the attributes, then must be divided into multiple tables, the third paradigm is the request has been divided into a number of tables, a table can only have another label ID, and no other information, (any other information, the primary key in another table query).

2. The first paradigm must be satisfied before the second paradigm can be satisfied, and the first second paradigm must be met in order to satisfy the third paradigm.

Two: The five major constraints in the database:

The five major constraints in the database include:

1. PRIMARY KEY constraint (Primay key coustraint) uniqueness, non-nullability;

2. Unique constraint (unique counstraint) uniqueness, can be null, but only one;

3. Default constraint (default counstraint) for this data;

4. Foreign KEY constraints (Foreign key Counstraint) need to establish a relationship between two tables;

5. Non-null constraint (NOT NULL counstraint): Sets a non-null constraint that cannot be empty.

Detailed Description:

(1) [FOREIGN KEY constraint (Foreign key counstraint)]
1. Considerations for setting foreign keys:
①: Only the INNODB database engine supports foreign keys, modify My.ini file Settings Default-storage-engine=innodb;
②: The data type of the foreign key and the reference column must be the same. (numeric type requires the same length and unsigned, string requirements are the same type, the length can be different);
③: The field that sets the foreign key must have an index, and if there is no index, an index is automatically generated when the foreign key is set;

2. Set the syntax for the foreign key:
[CONSTRAINT foreign Key name] FOREIGN Key (foreign key field) REFERENCES reference table (reference field);
[on DELETE set NULL on UPDATE CASCADE]--The set operation is complete.

3, FOREIGN KEY constraints of the reference operation:
How foreign keys in the foreign key table are to be addressed when a reference field for a reference table is deleted or updated.
Reference Operation Optional Values:
RESTRICT: Reject the deletion or modification of the Reference field (default);
NO ACTION: Same as restrict, but this command only takes effect in MySQL;
CASCADE: When deleting or updating the Reference field of a reference table, the record of the foreign key table is deleted or updated synchronously;
Set NULL: When the reference field for the referenced table is deleted or updated, the foreign key of the foreign key table is set to null (the foreign key cannot be set to NOT NULL at this time).

(2) [PRIMARY KEY constraint] (Primay key Coustraint)
1. Primary KEY considerations: Primary key default non-NULL, default uniqueness constraint, only the primary key can set autogrow (the primary key does not necessarily increment, self-increment must be the primary key).
2. How to set the primary key:

①: Set when defining the column: ID INT UNSIGNED PRIMARY KEY.
②: Set after the column definition is complete: PRIMARY KEY (ID).

Other constraints do not have special requirements and therefore do not explain.

Database basics: Constraints in databases and three paradigms