4. create and update data tables

A data table is an operation-level object in a relational database. In relational databases, data records are stored through data tables.Primary keys, foreign keys, constraints, and indexes are often used in data tables.This chapter gives a comprehensive introduction to the data types, primary keys, foreign keys, constraints, and indexes involved in the data table. In addition, we will introduce how to create data tables, how to add and modify records in tables, and how to delete data tables.
1. Tables in the database
In a relational database, a data table is the basic unit for storing data. It consists of rows and columns. It is converted based on the E-R diagram of the database design phase. In addition to basic concepts such as rows, columns, data records, attributes, and fields, data tables also involve primary keys, foreign keys, indexes, and constraints.
Data Records, rows, fields, and columns
In a relational database, a data table is the basic unit for storing data. It consists of rows and columns. Where,A row is used to describe the specific data in an object. The data in each row in a data table is called a data record or record. A field is a column in a table, used to save specific information in a record in a data table. A field can also be called a column in a relational database.
Column_name1 is the column name of the specified data table; datatype1 is the data type of the specified column name; constraint_condition1 is the Integrity Constraint of the specified column name.
Database constraints are required to ensure data integrity.Constraints mainly include UNIQUE, primary key, foreign key, CHECK, and non-NULL constraints ).
The UNIQUE constraint (UNIQUE) is used to ensure that a column or a group of columns do not have the same value. If a unique constraint is defined for a column, duplicate values are not allowed in the column, but NULL values (that is, NULL values) are allowed in the column ). A unique constraint can be defined at the table level or column level. Generally, after a column is created with a unique constraint, the database automatically creates a unique index for the column. The index name is the same as the constraint name.
A primary key constraint is used to ensure that a column or a group of columns that use primary key constraints have unique values and cannot contain NULL values (NULL values ). Each column in a data table can only define one primary key. Generally, after a primary key constraint is created for a column, the database automatically creates a primary index for the column. The index name is the same as the constraint name.
When creating a data table, if you want to combine multiple columns as the primary key of a data table, you can use parentheses after the primary key keyword, place the column to be defined as the primary key in brackets after the primary key keyword. Multiple columns in brackets must be separated by commas.
First, we should consider this problem. For some reason, we need to delete all the information of a student in the school. There are two tables involved in student information, one is the student information table and the other is the score information table. The student information table stores the basic information of the student, and the score information table stores the course score information of the student. If no foreign key constraint is defined, when the student is deleted from the student information table, the course score information of the student is not deleted, this will cause data inconsistency between the two associated tables.
The same problem may also occur when you modify data in two associated tables. If there is an association between multiple tables, foreign key constraints are not defined, similar to the above situation is likely to happen. To avoid data inconsistency in the associated tables caused by improper operations, it is necessary to define foreign key constraints for the associated tables.
The foreign KEY constraint is mainly used to define the relationship between two tables. The foreign key constraint ensures the integrity of the table's reference and ensures that data operations on a table do not adversely affect the associated table. The syntax format for defining foreign keys is as follows:

(Column name 1)]

The on update and on delete operations between the master and slave tables indicate the main operation methods used to modify and DELETE the data in the table.
CASCADE: CASCADE deletion. If a data record in the primary table is deleted, the corresponding data in the table will also be deleted.
Set null: Empty to delete. If a data record in the primary table is deleted, the corresponding data in the table will also be set to a null value.
RESTRICT: Restricted deletion. If a data record in the master table is deleted, the database management system reports an error when executing the DELETE command to notify the user that the data corresponding to the master table still exists in the slave table, however, the data corresponding to the primary table will not be deleted from the table. It is the default method.
CHECK constraints are used to restrict the value range or conditions of a column. Using CHECK constraints can ensure consistency of data rules. Multiple CHECK constraints can be defined for a column. After a CHECK constraint is defined for a column, the data in the column must meet the specified constraints.
A non-NULL constraint (not null) is used to ensure that NULL values (that is, NULL values) cannot be inserted when data is inserted into this column ). Non-null constraints can only be used to constrain columns.