Create and update data tables

Creating and updating 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. in a relational database, a table is a 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 are stored in a relational database. A data table is a basic unit of data storage and consists of rows and columns. 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. To ensure that two identical data records are not displayed in a data table, a primary key must be defined for each data table. The primary key is used as the unique identifier in the data table to ensure the uniqueness of a record. The primary key can be used to differentiate each record in a data table. When creating a data table, you must ensure that the column values of the columns defined as primary keys are unique and cannot be NULL ). The primary key is used to constrain Object Integrity in the relational model. The entity Integrity Constraint refers to the Integrity Constraint on the row of rows in the data table. In Entity integrity, a primary key is required to uniquely identify the data records of each row in the data table. The primary key in the data table is unique and cannot be null. Note: a primary key can be defined in multiple columns, not necessarily in one column. That is to say, when defining a data table, you can combine multiple columns in the data table as the primary key of the table. A foreign key is used to define the relationship between a table and a table. In A data table, the foreign key is defined as follows: If attribute column F is an attribute in relation B (not the primary key of relation B), and attribute column F is the primary key in relation, then F is the foreign key of relationship B. The table in relation A is called the master table, and the table in relation B is called the slave table of the master table. The foreign key is used in the relational model to constrain the integrity of the reference. A reference Integrity Constraint refers to a constraint between a table and a table. In reference integrity, the foreign key values in each data record in the table must exist in the master table. For two data tables with an association relationship, adding, modifying, or deleting a data table will affect the records in the other data table. In practical applications, indexes are generally used for queries to speed up access and reduce access time. By creating an index, the database can quickly locate the data in the data table without scanning the entire data table. An index is a pointer to data in a data table, pointing to the physical location of an index field in a data table. If the field specified in the WHERE clause is set as the index field during the query operation, the database first queries the specified value in the index, and return the position of the queried data in the data table. If the field specified in the WHERE clause is not set as the index field during the query operation, the database queries each row of data records in the data table for all scans. Therefore, appropriate index creation can accelerate data retrieval, improve data access efficiency, and provide data query performance. Of course, indexes also have some drawbacks. For example, indexes occupy a large amount of hard disk space. As data columns increase, the time required to create and maintain indexes also increases; when adding, deleting, and modifying data, you must maintain indexes to reduce the data update speed. Therefore, it is not suitable for creating indexes for columns that are not frequently used in the Query Process and columns that often require update operations such as adding, deleting, and modifying data tables. Although creating an index can increase the query speed, because the index itself occupies physical space and maintains the index may cause time loss, when creating an index for a column in a data table, instead of creating an index for each column in a data table, doing so will not improve the query efficiency. Therefore, you must create an index on the appropriate columns of the data table. You can create indexes in the following columns. 1. create an index in the primary key column. create an index on the frequently used connection column during multi-table join. create an index on a column that is frequently queried using the WHERE clause. 4. create an index on columns that are often grouped and sorted BY. To ensure data integrity, you must use database constraints. Integrity constraints include constraints on tables and columns. Table constraints mainly include UNIQUE constraints, primary key constraints, foreign key constraints, and CHECK constraints. Column constraints include UNIQUE constraints, reference constraints, and CHECK constraints, as well as non-empty constraint UNIQUE constraints (UNIQUE): ensure that the values of a column or a group of Columns with unique constraints do not have the same values, that is, ensure the uniqueness of the column values. However, the unique constraint allows NULL values to be inserted into columns ). Primary key: ensure that only columns with primary key constraints have unique values and cannot contain null values. Each column in a data table can only define one primary key. Foreign KEY constraint (foering key): ensures the integrity of the table's reference, and ensures that data operations on a table do not adversely affect the table associated with it. CHECK constraints: restrict the value range or conditions of a column. Multiple CHECK constraints can be defined for a column. Non-NULL constraint (not null): used only to constrain columns. NULL values cannot be inserted when data is inserted into this column. 2. After creating a data table, you can create a data table in the database. You can use the create table statement to CREATE a data TABLE. The syntax for creating a data TABLE using the creat table statement is as follows:

CREATE TABLE table_name(column_name1 datatype1 [constraint_condition1][,column_name2 datatype2 [constraint_condition2]]…)

 

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:

Foreign key [Table Name 1] (column name 1) REFERENCES table name 2 (column name 2) [on update [CASCADE] | [set null] | [RESTRICT] [on delete [CASCADE] | [set null] | [RESTRICT]

 

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.