MySQL 5-2-Constraints

1. The primary key constraint primary key is a group of one or more columns in the Table. Its value uniquely identifies each row in the table. You can create a primary key by defining the primary key constraint, and the column in the primary key constraint cannot take null values. Because the primary key constraint ensures that the data is unique, it is often used to define the flag column. When the primary key constraint is defined for a table, MySQL creates a unique index for the primary key column to ensure data uniqueness. When a primary key is used in a query, the index can be used to quickly access data. If the primary key constraint is defined by a combination of multiple columns, the values of a column can be repeated, but the combination values of all columns in the primary key constraint definition must be unique. You can define a primary key in two ways: as a column or table Integrity Constraint. As a column Integrity Constraint, you only need to add the keyword primary key when defining the column. As a table Integrity Constraint, you must add a primary key (col_name,…) at the end of the statement ,...) Statement. For example, create table XS1 and define the name as the primary key. Create table XS1 (student ID varchar (6) NULL, name varchar (8) not null primary key, date of birth datetime); www.2cto.com Description: In this example, the primary key is defined after NULL is specified, if this parameter is null, it can be specified after the primary key. when the primary key of a table is a composite primary key (that is, when two fields are primary keys), they can only be defined as integrity constraints of the table. For example, create a course table to record the student ID, name, course number, credits, and graduation date for each course. The student ID, course number, and graduation date constitute a composite primary key. Create table course (student ID varchar (6) not null, name varchar (8) not null, graduation date NOTNULL, course No. varchar (3), credits tinyint, primary key (student ID, course number, graduation date); If a column that is part of the primary key is NOT defined as not null, MySQL automatically defines this column as not null. In fact, in the example, the not null statement in the name column can be ignored, but it is best to include this NULL parameter for clarity. 2. In principle, any combination of columns or columns can act as a primary key. The primary key column must comply with some rules. These rules are derived from Relational Model Theory and MySQL rules: (1) Each table can only define one primary key. This rule from the relational model also applies to MySQL. (2) The relational model theory requires that a primary key be defined for each table. However, MySQL does not require this. You can create a table without a primary key. However, a primary key should be specified for each base table in terms of security. The main reason is that there is no primary key and two identical rows may be stored in a table. Therefore, the two rows cannot be differentiated from each other. During the query process, they will meet the same conditions and will always be updated together during the update process, which may cause the database to crash. (3) Two different rows in the table cannot have the same value on the primary key. This is the uniqueness rule. (4) If a column is deleted from a composite primary key and the remaining columns constitute the primary key still meet the uniqueness principle, the composite primary key is incorrect, this rule is called a minimal rule ). That is to say, the composite primary key should not contain an unnecessary column. (5) A column name can only appear once in a primary key column table. MySQL automatically creates an index for the primary key. Generally, this index is named PRIMARY. However, you can rename this index. Example: Create the course table in example 5.9 and name the index created by the primary key as INDEX_course. Www.2cto.com create table course (student ID varchar (6) not null, name varchar (8) not null, credits tinyint, primary key INDEX_course (student ID, course number); 3. replace key constraint (unique) in the relational model, the replace key is a column or a group of columns in the table like a primary key. Their values are unique at all times. The replacement key is a candidate key that is not selected as the primary key. The keyword defining the replacement key is UNIQUE. For example, in table XS1, the name column is defined as an alternative key. Create table XS1 (student ID varchar (6) NULL, name varchar (8) not nullunique, date of birth datetime NULL, primary key (student ID); Description: the keyword UNIQUE indicates that "name" is an alternative key, and its column value must be UNIQUE. The replacement key can also be defined as a TABLE Integrity Constraint. The preceding statement can be defined as follows: create table XS1 (student ID varchar (6) NULL, name varchar (8) NOTNULL, date of birth datetime NULL, primary key (student ID), UNIQUE (name); 4. in MySQL, the main differences between replacement keys and primary keys are as follows. (1) only one primary key can be created for a data table. However, a table can have several UNIQUE keys, and they can even overlap. For example, an alternative key is defined on the C1 and C2 columns, in addition, another alternative key is defined on C2 and C3. The two replicas overlap on the C2 column, and MySQL allows this. The value of the primary key field of www.2cto.com (2) cannot be NULL. The value of the UNIQUE field can be NULL, but must be declared using NULL or NOTNULL. (3) When you create a primary key constraint, the system automatically generates a primary key index. When a UNIQUE constraint is created, the system automatically generates a UNIQUE index. The primery key constraint and UNIQUE constraint can be used to implement the so-called entity Integrity Constraint of a table. Values not allowed in columns defined as primery key and unique key. 5. A foreign key (foreign) is a special integrity constraint and is implemented as a foreign key. Therefore, the student ID column and course number column in The XS_KC table can be defined as a foreign key. You can define a foreign key declaration when creating or modifying a table. The syntax format for defining foreign keys has been provided during the introduction of indexes. The reference_definition definition is listed here. Syntax format: REFERENCES tbl_name [(index_col_name,...)] [on delete {RESTRICT | CASCADE | set null | no action}] [on update {RESTRICT | CASCADE | set null | no action}] Description: foreign keys are defined as integrity constraints of tables. reference_definition contains the tables and columns referenced by foreign keys. You can also declare Reference Actions. ● Tb1_name: name of the table referenced by the foreign key. This table is called the referenced table. The table where the foreign key is located is called a reference table. ● Index_col_name: Format: col_name [(length)] [ASC | DESC] col_name: name of the referenced column. The foreign key can reference one or more columns. All column values in the foreign key must exist in all referenced columns. Foreign keys can only reference primary keys and alternative keys. A foreign key cannot reference a random group of columns in the referenced table. It must be a combination of columns in the referenced table and its values must be unique. ● On delete | ONUPDATE: You can define Reference Actions for each foreign key. The reference action contains two parts: In the first part, specify the statement that the reference action applies. There are two related statements, UPDATE and DELETE. In the second part, specify the action to take. Possible actions are restrict, cascade, set null, no action, and SETDEFAULT. The following describes the meanings of these different actions. RESTRICT: When you want to delete or update the values in the reference columns of the parent table, you are not allowed to delete or update the parent table. CASCADE: automatically deletes or updates the matched rows in the sub-table When deleting or updating rows from the parent table. Set null: When deleting or updating a row from the parent table, SET the foreign key column corresponding to the child table to NULL. This is legal if the not null qualifier is NOT specified for the foreign key column. No action: no action means no action is taken. If there is a related foreign key value in the referenced table, attempts to delete or update the primary key value in the parent table are not allowed, same as RESTRICT. Set default: Same as set null, except that set default refers to the foreign key column in the stator table as the DEFAULT value. If no action is specified, two reference actions use RESTRICT by default. FOREIGN keys can only be used in tables created using the InnoDB Storage engine. For other types of tables, the MySQL server can parse the foreign key syntax in the create table statement, but cannot use or save it. For example, to create an XS1 table, the student ID of all XS tables must appear in the XS1 table. Assume that the XS table has been created using the student ID column as the primary key. Www.2cto.com create table XS1 (student ID varchar (6) NULL, name varchar (8) NOTNULL, date of birth datetime NULL, primary key (name), foreign key (student ID) references xs (student ID) ondelete restrict on update restrict); 6. when a foreign key is specified, the following rules apply: (1) the referenced TABLE must have been created using a create table statement, or must be the currently created TABLE. In the latter case, the reference table is the same table. (2) The primary key must be defined for the referenced table. (3) The column name (or a combination of column names) must be specified after the table name of the referenced table ). This column (or column combination) must be the primary key or alternative key of the table. (4) Although the primary key cannot contain null values, a null value can appear in the foreign key. This means that the content of the foreign key is correct as long as each non-null value of the foreign key appears in the specified primary key. (5) the number of columns in the foreign key must be the same as the number of columns in the primary key of the referenced table. (6) the data type of the columns in the foreign key must be the same as the data type of the columns in the primary key of the referenced table. The referenced table and referenced table related to the foreign key can be the same table. Such a table is called a self-referencing table. This structure is called self-referentialintegrity ). For example, you can CREATE such an XS1 TABLE: create table XS1 (student ID varchar (6) NOTNULL, name varchar (8) NOTNULL, date of birth datetime NULL, primary key (student ID ), foreign key (student ID) REFERENCES XS1 (student ID); www.2cto.com 7. check constraints each database has some dedicated integrity constraints. For example, the number of stars in the KC table must be between 1 and 1 ~ The date of birth in the XS table must be later than January 7, January 1, 1986. Such rules can be specified using CHECK integrity constraints. CHECK integrity constraints are defined when a table is created. It can be defined as a column Integrity Constraint or a table Integrity Constraint. Syntax format: CHECK (expr) Description: expr is an expression that specifies the conditions to be checked. When updating table data, mySQL checks whether the updated data row meets the CHECK condition. Example 1: Create the student table. Only the student ID and Gender columns are considered. Gender can only contain male or female. Create table student (student ID char (6) not null, Gender char (1) not null check (Gender IN ('male', 'female '))); here, CHECK integrity constraints specify the value permitted by gender. Because CHECK is included in the column's own definition, CHECK integrity constraints are defined as column integrity constraints. Example 2: Create the student1 table. Only the student ID and birth date columns are considered. The birth date must be later than January 1, January 1, 1980. Create table student1 (student ID char (6) not null, date of birth not null check (date of Birth> '2017-01-01 ')); the expressions used in the preceding CHECK integrity constraints are simple. MySQL allows more complex expressions. For example, you can add a subquery to a condition. For example, create the student2 table and only consider the student ID and Gender columns, and confirm that all values in the Gender column are from the Gender column of the student table. Create table student2 (student ID char (6) not null, Gender char (1) not null check (Gender IN (SELECT gender FROM student); If the specified integrity constraints, to compare two or more columns in a table, the Integrity Constraint of this column must be defined. For example, create student3, which has three columns: Student ID, best score, and average score. The best score must be greater than the average score. Create table student3 (www.2cto.com student ID char (6) not null, best score INT (1) not null, average score INT (1) not null, CHECK (best score> average score); you can also define multiple CHECK integrity constraints, separated by commas. However, in the current MySQL version, CHECK integrity constraints have not been enhanced. The CHECK constraints defined in the preceding example are analyzed by MySQL but ignored. That is to say, the CHECK constraint here is only a comment for the moment and does not play any role. I believe it can be expanded in future versions. 8. CONSTRAINT keywords if an INSERT, UPDATE, or DELETE statement violates the integrity constraints, MySQL returns an error message and rejects updates. An UPDATE may result in violations of multiple integrity constraints. In this case, the application obtains several error messages. To precisely indicate which Integrity Constraint is violated, you can assign a name for each Integrity Constraint. Then, an error message contains this name, making the message more meaningful to the application. The constraint keyword is used to specify the name of the Integrity constraint. Syntax format: CONSTRAINT [symbol] symbol is the specified name. This name is defined before the Integrity CONSTRAINT and must be unique in the database. If it is not given, MySQL automatically creates this name. You can only specify names for table integrity constraints, but not column integrity constraints. Example: Create an XS1 table that is the same as the XS1 table in example 5.8 and name the primary key. Create table XS1 (student ID varchar (6) NULL, name varchar (8) not null, date of birth datetime NULL www.2cto.com CONSTRAINTPRIMARY_KEY_XS1 primary key (name); Description: in this example, the name PRIMARY_KEY_XS1 is assigned to the primary key name. When defining integrity constraints, assign names as much as possible so that they can be referenced more easily when you delete integrity constraints. This means that the table Integrity Constraint is more popular than the column Integrity Constraint because it is impossible to assign a name to the latter. 9. drop table statement (delete) if you use a drop table statement to delete a TABLE, all integrity constraints are automatically deleted. All foreign keys of the referenced TABLE are also deleted. Using the alter table statement, integrity can be deleted independently without deleting the TABLE itself. The syntax for deleting an index is the same as that for deleting an index. For example, delete the primary key of table XS. Alter table xs drop primary key;
Author tianyazaiheruan