Integrity constraint integrity constraints are for the correctness of table data! If the data is incorrect, it cannot be added to the table at the outset.1 PRIMARY KeyWhen a column has a primary KEY constraint added, the data for that column cannot be repeated. In this way, the value of its primary key column in each row of records is the unique identifier for this row. For example, the student's school number can be used to make a unique identification, and the student's name is not uniquely identifiable, because the student name may be the same name. The value of the primary key column cannot be null, nor can it be duplicated! Specifying a PRIMARY KEY constraint using the primary key keyword
- CREATE TABLE: Specify a primary key when defining a column:
CREATE TABLE Stu (Sid CHAR (6) PRIMARY KEY, sname VARCHAR (20), Age INT, Gender VARCHAR (10));
- CREATE TABLE: Specify the primary key independently after the column is defined:
CREATE TABLE Stu (Sid CHAR (6), sname VARCHAR), age INT, Gender VARCHAR (Ten), PRIMARY KEY (SID));
- To specify a primary key when modifying a table:
ALTER TABLE stuadd PRIMARY KEY (SID);
- Delete primary KEY (just delete primary KEY constraint without deleting primary key column):
ALTER TABLE Stu DROP PRIMARY KEY;2 primary key self-growthMySQL provides automatic growth of the primary key function! When the primary key is set to autogrow, the value of the primary key is automatically generated when the primary key value is not given, and it is the maximum primary key value of +1, and there is no possibility of repeating the primary key.
- Set primary key self-growth when creating tables (primary key must be integer to self-grow):
CREATE TABLE Stu (Sid INT PRIMARY KEY auto_increment, sname VARCHAR (20), Age INT, Gender VARCHAR (10));
- To set the primary key self-growth when modifying a table:
ALTER TABLE stu change Sid Sid INT Auto_increment;
- Delete primary key self-growth when modifying table:
ALTER TABLE stu change Sid Sid INT;3 non-emptyA column that specifies a non-null constraint cannot have a value, meaning that a column with a non-null constraint added must be given a value when the record is inserted, and the value of a non-empty column cannot be set to NULL when the record is modified.
- Specify a non-null constraint:
CREATE TABLE Stu (Sid INT PRIMARY KEY auto_increment, sname VARCHAR () not NULL, Age INT, Gender VARCHAR (10)); When you insert a record into a Stu table, you must specify a value for the Sname field when you specify non-empty for the sname field, or you will get an error: INS ERT into Stu (SID) VALUES (1); The inserted record does not have a specified value in Sname, so an error will be given!4 UniqueYou can also specify a unique constraint for a field! When you specify a unique constraint for a field, the value of the field must be unique. This is similar to the primary key! For example, specify a unique constraint for the sname field of the Stu table: CREATE table Tab_ab (SID INT PRIMARY KEY auto_increment, sname VARCHAR () unique); INSERT INTO sname (SID, Sname) VALUES (1001, ' ZS ') and insert into sname (SID, Sname) VALUES (1002, ' ZS '); When you insert the same name two times, MySQL will error!5 FOREIGN KeyThe primary foreign key is the only way to make the table associated with the table! The foreign key is the primary key of the other table! For example, there is an association between the employee table and the departmental table, where the department number field in the Employee table is a foreign key and a foreign key relative to the department table. For example, a record in the T_section table with a SID of 1 indicates that there is a taxonomy called Java, and the moderator is a user with a UID of 1 in the T_user table, i.e. zs! For example, the record for Tid 2 in the T_topic table is a post with the name "Java is Coffee", which is a post in the Java section, and its author is ww. Foreign keys are the values used to constrain this column must be the primary key value of another table!!!
- Create the T_user table, specifying the UID as the primary key column:
CREATE TABLE t_user (uid INT PRIMARY KEY auto_increment, uname VARCHAR () UNIQUE not NULL);
- Create the T_section table, specify the SID as the primary key column, and u_id the foreign key for the UID column of the relative T_user table:
CREATE TABLE t_section (Sid INT PRIMARY KEY auto_increment, sname VARCHAR (30), u_id INT, CONSTRAINT fk_t_user FOREIGN KEY (u_id) REFERENCES T_user (UID));
- Modify the T_section table to specify U_ID as the foreign key for the UID column of the relative T_user table:
ALTER TABLE t_sectionadd CONSTRAINT fk_t_userforeign KEY (u_id) REFERENCES t_user (UID);
- Modify the T_section table to remove the foreign KEY constraint for u_id:
ALTER TABLE t_sectiondrop FOREIGN KEY fk_t_user;6 table-to-table relationships
- One-to: for example T_person and T_card tables, i.e. people and identity cards. This situation requires finding out the master-slave relationship, i.e. who is the primary table and who is from the table. People can have no ID, but the ID must be someone to do, so the person is the main table, and the identity card is from the table. There are two possible scenarios for designing from a table:
- Add a Foreign key column (relative to the T_user table) in the T_card table, and add a unique constraint to the foreign key;
- Add a FOREIGN KEY constraint (relative to the T_user table) to the primary key of the T_card table, that is, the primary key of the T_card table is also a foreign key.
- One-to-many (multi-pair): The most common is a pair of more! A pair of many and many to one, which is from which angle to see. T_user and T_section relations, from the T_user view is a one-to-many, and from the t_section point of view is a lot of a! This situation is created in the multi-party foreign key!
- Many-to-many: for example, T_stu and T_teacher tables, where a student can have multiple teachers, and a teacher can have multiple students. This situation often requires creating an intermediate table to handle many-to-many relationships. For example, create a table T_stu_tea table, give two foreign keys, a foreign key relative to the T_stu table, and another foreign key relative to the T_teacher table.
SQL statement integrity constraints in the database
