Shulou(Shulou.com)05/31 Report--

Xiaobian to share with you the database sql integrity constraint example analysis, I believe most people do not know how, so share this article for your reference, I hope you read this article after a lot of harvest, let us go to understand it!

integrity constraints

Integrity constraints are for the correctness of table data! If the data is incorrect, 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 that column cannot be repeated. The value of the primary key column in each row of records is the unique identifier for that row. For example, the student's student number can be used as a unique identifier, while the student's name cannot be used as a unique identifier because the student's name may have the same name.

The value of primary key column cannot be NULL or duplicate!

Specify primary key constraints using the PRIMARY KEY keyword

Creating tables: Specify primary keys when defining columns:

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

Create a table: Specify the primary key independently after defining the column:

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

Specify primary keys when modifying tables:

ALTER TABLE stuADD PRIMARY KEY(sid);

Delete primary keys (only primary key constraints are deleted, primary key columns are not deleted):

ALTER TABLE stu DROP PRIMARY KEY;

2 Primary key self-growth

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

Set the primary key to grow automatically when creating the table (primary key must be integer to grow automatically):

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

Set primary key self-growth when modifying 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 Not empty

The column specified with non-null constraint cannot have no value, that is, when inserting records, the column added with non-null constraint must be given a value; when modifying records, the value of non-null column cannot be set to NULL.

Specify non-null constraints:

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

When the sname field is specified as non-empty, when inserting records into the stu table, you must specify a value for the sname field, 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 field, then the value of the field must be unique. This is similar to the primary key! For example, specify a unique constraint on 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');

MySQL reports an error when inserting the same name twice!

5 Foreign key

Primary and foreign keys are the only way to form a table-to-table association!

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

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

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

Foreign key is used to constrain the value of this column must be the primary key value of another table!

Create t_user table, specify uid as primary key column:

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

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

ALTER TABLE t_sectionADD CONSTRAINT fk_t_userFOREIGN KEY(u_id)REFERENCES t_user(uid);

Modify t_section table to delete foreign key constraint of u_id:

ALTER TABLE t_sectionDROP FOREIGN KEY fk_t_user;

6 Relationships between tables

One-to-one: For example, t_person table and t_card table, that is, person and ID card. This situation requires finding out the master-slave relationship, i.e. who is the master table and who is the slave table. A person may not have an ID card, but an ID card must have a talent. Therefore, a person is the master table, while an ID card is the slave 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 unique constraints to the foreign key;

Add a foreign key constraint (relative to t_user table) to the primary key of t_card table, that is, the primary key of t_card table is also a foreign key.

One-to-many (many-to-one): The most common is one-to-many! One to many and many to one, this is from which angle to see. The relationship between t_user and t_section is one-to-many from the perspective of t_user, and many-to-one from the perspective of t_section! This is the case in many ways to create foreign keys!

Many-to-many: For example, t_stu and t_teacher tables, that is, a student can have multiple teachers, and a teacher can also have multiple students. This situation usually requires the creation of intermediate tables to handle many-to-many relationships. For example, create a table t_stu_tea, giving two foreign keys, one relative to t_stu and one relative to t_teacher.

The above is "sql integrity constraints in the database sample analysis" all the content of this article, thank you for reading! I believe that everyone has a certain understanding, hope to share the content to help everyone, if you still want to learn more knowledge, welcome to pay attention to the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.