Analysis of SQL integrity constraint statements in database

Integrity constraints

Integrity constraints are for the correctness of the table data! If the data is incorrect then it cannot be added to the table in the first place.

1 Primary key

When a primary key constraint is added to a column, the data in this column cannot be repeated. In this way, the value of the primary key column in each row of records is the unique identifier of this row. For example, a student’s ID number can be used as a unique identifier, but the student’s name cannot be used as a unique identifier because students may have the same name.
The value of the primary key column cannot be NULL or repeated!

To specify a primary key constraint, use the PRIMARY KEY keyword.

Create a table: Specify the primary key when defining the columns:

CREATE TABLE stu(
         sid CHAR(6) PRIMARY KEY,
         sname VARCHAR(20),
         age INT,
         gender VARCHAR(10)
);

Create a table: define the columns and then specify the primary key separately:

CREATE TABLE stu(
         sid CHAR(6),
         sname VARCHAR(20),
         age INT,
         gender VARCHAR(10),
         PRIMARY KEY(sid)
);

Specify the primary key when modifying a table:

ALTER TABLE stu
ADD PRIMARY KEY(sid);

To delete a primary key (just delete the primary key constraint, not the primary key column):

ALTER TABLE stu DROP PRIMARY KEY;

2. Primary key auto-increment

MySQL provides the function of automatic growth of primary keys! When the primary key is set to auto-increment, if no primary key value is given, the primary key value will be automatically generated, and it will be the maximum primary key value + 1, so there will be no possibility of duplicate primary keys.

Set the primary key to auto-grow when creating a table (the primary key must be an integer to auto-grow):

CREATE TABLE stu(
         sid INT PRIMARY KEY AUTO_INCREMENT,
         sname VARCHAR(20),
         age INT,
         gender VARCHAR(10)
);

Set the primary key auto-increment when modifying the table:

ALTER TABLE stu CHANGE sid sid INT AUTO_INCREMENT;

Delete the primary key auto-increment when modifying the table:

ALTER TABLE stu CHANGE sid sid INT;

3 Not empty

The column that specifies the non-empty constraint cannot be without a value. That is to say, when inserting a record, a value must be given to the column that has the non-empty constraint added; when modifying a record, the value of the non-empty column cannot be set to NULL.

Specify a not null constraint:

CREATE TABLE stu(
         sid INT PRIMARY KEY AUTO_INCREMENT,
         sname VARCHAR(10) NOT NULL,
         age INT,
         gender VARCHAR(10)
);

After specifying the sname field as non-empty, you must specify a value for the sname field when inserting records into the stu table, otherwise an error will be reported:

INSERT INTO stu(sid) VALUES(1);

There is no specified value for sname in the inserted record, so an error will be reported!

4 Unique

You can also specify unique constraints for fields! When a unique constraint is specified for a column, the values ​​in the column must be unique. This is similar to a primary key! For example, to specify a unique constraint for the sname field of the stu table:

CREATE TABLE tab_ab(
     sid INT PRIMARY KEY AUTO_INCREMENT,
     sname VARCHAR(10) UNIQUE
);
 
  INSERT INTO sname(sid, sname) VALUES(1001, 'zs');
INSERT INTO sname(sid, sname) VALUES(1002, 'zs');

When you insert the same name twice, MySQL will complain!

5 Foreign Keys

Primary and foreign keys are the only way to associate tables!

A foreign key is another table's primary key! For example, there is an association relationship between the employee table and the department table, in which the department number field in the employee table is a foreign key, which is a foreign key relative to the department table.

For example, in the t_section table, the record with sid 1 indicates that there is a category called java, and the moderator is the user with uid 1 in the t_user table, that is, zs!

For example, in the t_topic table, the record with tid 2 is a post named "Java is coffee", which is a post in the java section and its author is ww.

The foreign key is used to constrain the value of this column to be the primary key value of another table! ! !

Create the t_user table and specify uid as the primary key column:

CREATE TABLE t_user(
     uid INT PRIMARY KEY AUTO_INCREMENT,
     uname VARCHAR(20) UNIQUE NOT NULL
);

Create the t_section table, specify sid as the primary key column, and u_id as the foreign key to the uid column of the 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 and specify u_id as the foreign key to the uid column of the t_user table:

ALTER TABLE t_section
ADD CONSTRAINT fk_t_user
FOREIGN KEY(u_id)
REFERENCES t_user(uid);

Modify the t_section table and delete the foreign key constraint of u_id:

ALTER TABLE t_section
DROP FOREIGN KEY fk_t_user;

6 Relationships between tables

• One-to-one: For example, the t_person table and the t_card table, that is, a person and an ID card. In this case, you need to find out the master-slave relationship, that is, who is the master table and who is the slave table. A person may not have an ID card, but an ID card must exist under the person, so the person is the primary table and the ID card is the secondary table. There are two options for designing a slave table:
• Add a foreign key column to the t_card table (relative to the t_user table) and add a unique constraint to the foreign key;
• Add a foreign key constraint to the primary key of the t_card table (relative to the t_user table), that is, the primary key of the t_card table is also a foreign key.
• One to many (many to one): The most common one is one to many! One-to-many and many-to-one, from which perspective can we look at this? The relationship between t_user and t_section is one-to-many from the perspective of t_user, but many-to-one from the perspective of t_section! In this case, foreign keys are created on multiple parties!
• Many-to-many: For example, the t_stu and t_teacher tables, that is, one student can have multiple teachers, and one teacher can have multiple students. This situation usually requires the creation of an intermediate table to handle the many-to-many relationship. For example, create another table t_stu_tea and give it two foreign keys, one relative to the t_stu table and the other relative to the t_teacher table.

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.