Summary of common MySQL constraints

Previous writing is too messy, turn it out and reorganize it

Small Catalog Series:

(1) Getting started with MySQL is not a big problem

(1) introducing constraints

(1) Where do constraints occur?

To explain constraints, you need to know where and why constraints are used.

The SQL language completes the definition of a relational schema by defining a basic table corresponding to a relationship in the following statement format:

CREATE TABLE table name (
    <Column Name 1> <Data Type 1> [<Column Level Complete Constraints>],
    [<Column Name 2> <Data Type 2> [<Column Level Complete Constraints>],...],
    [<Table-level Complete Constraints>]
);

Symbol Rules: When some definitions are shown below, use Chinese symbols to express them for easy understanding (with specific examples, don't worry if you can't understand them)

<>The content in is the actual semantics
The content in [] is optional (or optional)
Content in {} must be explicitly specified
| as Option Character
[,...n] means that the previous item can be repeated multiple times

(2) What are constraints used for?

Constraints are constraints on attribute values. They are called column-level constraints and table-level constraints for multicolumn attributes.

How do you understand that?For example, if a column is called a student number, we specify a constraint. This line does not allow NULL, and we can also specify it as the primary key so that the student number can find a unique record of the student, such as foreign key knowledge, and so on.

To summarize, constraints are used to limit the data in a table to ensure its correctness, validity, and integrity.

Similarly, with the padding of knowledge about constraints, we can derive some of the following knowledge, such as multi-table operations, etc. So constraints are simple, but also very important.

(2) Common constraints

(1) Primary key constraints

A: Basic concepts

In a relationship model, the primary key is essentially a candidate key

Understanding is very simple, that is, you can use the primary key to determine a unique record: for example, the number is a candidate key for the student entity, a number can determine which student this student is, and we do not choose a name because, in practice, a name cannot be used as a unique identifier to identify a unique student record

Candidate key: The smallest set of attributes in a relationship that uniquely identifies a tuple

B: Features

Columns identified as primary keys cannot be empty or duplicated!!!

C: Specific operation

Specifies the primary key constraint, using the PRIMARY KEY keyword

In general, primary key constraints are used primarily to create tables, and there are two ways to specify constraints:

1 Defined after column

CREATE TABLE students  (
  sid INT(8) PRIMARY KEY,
  sname VARCHAR(5),
  department VARCHAR(32),
  birthday date
)

(2) Independent definition

CREATE TABLE students  (
  sid INT(8),
  sname VARCHAR(5),
  department VARCHAR(32),
  birthday date,
  PRIMARY KEY (sid) 
)

If the table has been created, you can also specify and delete primary keys in the following two ways

1. Delete the primary key

ALTER TABLE students DROP PRIMARY KEY;

(2) Specify the primary key

ALTER TABLE students ADD PRIMARY KEY(sid);

ALTER TABLE students MODIFY sid INT PRIMARY KEY;

D: Primary key increases by itself

When referring to the primary key, it must be mentioned that the primary key has increased. This function is also very common. When setting the active self-increasing, such as when you use advanced language, operate the database, insert a record into the student table, even if the primary key value is not given, the primary key value will be automatically generated and will be + 1 on the basis of the maximum primary key value, such as 0, 1, 2..., n

Most importantly, the primary key must be integer in order to increase itself.

If the primary key, such as sid, is of varchar type, there will be an error: Incorrect column specifier for column'sid'

Similarly, primary key self-increment is typically used when creating tables, using AUTO_INCREMENT, directly after the column name

CREATE TABLE students  (
  sid INT(8) PRIMARY KEY AUTO_INCREMENT,
  sname VARCHAR(5),
  department VARCHAR(32),
  birthday date
)

If the table has been created, you can also make self-increasing changes

1. Set the primary key to increase by itself

ALTER TABLE students CHANGE sid sid INT AUTO_INCREMENT;

ALTER TABLE students MODIFY sid INT AUTO_INCREMENT;

(2) Delete the primary key and increase itself

ALTER TABLE students CHANGE sid sid INT;

ALTER TABLE students MODIFY sid INT;

Description: The above settings and deletions give both CHANGE and MODIFY. What is the difference?

In fact, careful friends can also see that after CHANGE, there is one more column name sid (can be modified), so summarize as follows:

Modify type only with MODIFY
Modify both column names and types with CHANGE

(2) Non-null constraints

Non-null constraints are well understood, that is, the value specified for a non-null constrained column cannot be null, and we use NOT NULL to do this

CREATE TABLE students  (
  sid INT(8) PRIMARY KEY AUTO_INCREMENT,
  sname varchar(5) NOT NULL, -- sname Not empty
  department varchar(32),
  birthday date
);

Simply, we've added a non-empty constraint to the sname field (column) when it was created, and if the sname was NULL when it was inserted, we would have missed Column'sname'cannot be null

What if the table is already created?

After creating the table, add a non-empty constraint

ALTER TABLE students MODIFY sname VARCHAR(5) NOT NULL;

Remove non-empty constraints from sname

ALTER TABLE students MODIFY sname VARCHAR(5);

(3) Unique constraints

The only constraint is to specify that the values of this field (column) must be unique, which feels like a primary key, such as specifying sname cannot rename when we ask below to create a table

CREATE TABLE students  (
  sid INT(8) PRIMARY KEY AUTO_INCREMENT,
  sname VARCHAR(5) NOT NULL UNIQUE, -- sname Only
  department VARCHAR(32),
  birthday date
);

If you add two records with duplicate names, you will get an error

INSERT INTO students VALUES (NULL,'Zhang San','Computer Department','2020-06-16');
INSERT INTO students VALUES (NULL,'Zhang San','The Department of Business Administration','2019-06-16');

Error message: Duplicate entry'Zhang San'for key'sname'

Similarly, if you have created a table, how do you set or delete unique constraints?

After creating the table, add a unique constraint

ALTER TABLE students MODIFY sname VARCHAR(8) UNIQUE;

Delete unique constraints (essentially, delete indexes)

ALTER TABLE students DROP INDEX sname;
-- Both methods are possible
drop index sname on students;

(4) Foreign key constraints

A: Conceptual understanding

The theoretical definition of foreign keys is quite complex. I mentioned it in a database theory article written in the previous public number, but this one focuses on the use of MySQL, so let's change the theory into examples and colloquial jargon. Let's start with a question:

The student entity and the course entity are represented by the relation "student" and "course", respectively, and the relation between them is represented by the relation "course selection"

Student (number, name, department, birthday)
Course (number, title, instructor)
Course Selection (Number, Course Number, Score)

Question: Determine candidate keys, primary keys, foreign keys for each relationship

The student number can confirm that the only student is a candidate key and can be used as the primary key. Names need not be renamed, but there is no guarantee that no Rename is inappropriate. The course number can confirm that the only course is a candidate key and can be used as the primary key while the selected course (sc_relation) requires both the number of the course and the number of the course to determine the unique value, so both form the candidate key and act as the primary key
The number in the course selection relationship (sc_relation.sid) and Course Number (sc_relation.cid), representing the foreign keys of the course selection relationship, which correspond to the number of the student relationship.students.sid) Course number for course relationship (Course.sid(You don't have to have the same name, but it's usually written with the same name for understanding)
Simulate several simple tables to give you an intuitive understanding
- Description: The first is the student form, the second is the course, and the third is the optional course sc_relation

After looking at this example, does it feel a lot clearer from the understanding, so let's do it in practice:

C: Basic format

CREATE TABLE table name (
        ....
        CONSTRAINT foreign key name FOREIGN KEY REFERENCES primary table name (primary table column name)
);

--Delete foreign keys after creating tables
 ALTER TABLE table name DROP FOREIGN KEY foreign key name;

--After creating the table, add a foreign key
 ALTER TABLE table name ADD CONSTRAINT foreign key name FOREIGN KEY (foreign key field name) REFERENCES primary table name (primary table column name);

B: Specific operation

Let's follow this map

Create student table with student number sid as primary key

CREATE TABLE students (
    sid INT(8) PRIMARY KEY AUTO_INCREMENT,
    sname VARCHAR(5) NOT NULL UNIQUE,
    department VARCHAR(32),
    birthday date
);

Create course number cid as primary key

CREATE TABLE course (
    cid INT(8) PRIMARY KEY AUTO_INCREMENT,
    cname VARCHAR(5),
    teacher VARCHAR(32)
);

Create Course Selection Relationship Table, sc_sid, sc_cid is a foreign key, pointing to the sid number in the student table and the cid number in the course table

CREATE TABLE sc_relation  (
    sid INT(8),
    cid INT(8),
    cscore VARCHAR(5),
    CONSTRAINT sc_sid FOREIGN KEY (sid) REFERENCES students(sid),
    CONSTRAINT sc_cid FOREIGN KEY (cid) REFERENCES course(cid)
);

Provide some random data for you to test

-- Insert Student Data
INSERT INTO students VALUES (1001, 'King Five', 'The Department of Business Administration', '2020-06-16');
INSERT INTO students VALUES (1002, 'Tom', 'Department of Music and Dance', '2020-06-16');
INSERT INTO students VALUES (1003, 'jack', 'Department of Fine Arts', '2020-06-16');

-- Insert course data
INSERT INTO course VALUES (1, 'College English', 'Teacher 1');
INSERT INTO course VALUES (2, 'University Physics', 'Teacher 2');
INSERT INTO course VALUES (3, 'data base', 'Teacher 3');
INSERT INTO course VALUES (4, 'operating system', 'Teacher 4');
INSERT INTO course VALUES (5, 'Advanced mathematics', 'Teacher 5');

-- Insert Course Selection Data
INSERT INTO sc_relation VALUES (1001, 2, '88');
INSERT INTO sc_relation VALUES (1001, 3, '92');
INSERT INTO sc_relation VALUES (1001, 4, '78');
INSERT INTO sc_relation VALUES (1001, 5, '83');
INSERT INTO sc_relation VALUES (1002, 1, '77');
INSERT INTO sc_relation VALUES (1002, 2, '90');
INSERT INTO sc_relation VALUES (1002, 5, '89');
INSERT INTO sc_relation VALUES (1003, 1, '86');
INSERT INTO sc_relation VALUES (1003, 6, '88');
INSERT INTO sc_relation VALUES (1003, 6, '82');

What's the use?At this time, the student table and the course table form a relationship with the selected course table. When the visual software edits and inserts, some insertible choices will be given by default, which is automatically found by the software based on the foreign key relationship you set up.

What happens when you create a table?

Delete foreign keys after creating tables

ALTER TABLE sc_relation DROP FOREIGN KEY sc_sid;

After creating the table, add a foreign key

ALTER TABLE sc_relation ADD CONSTRAINT sc_sid FOREIGN KEY (sid) REFERENCES students(sid)

C: Cascade operations

If you have stored information about the course selection for the student with the student number 1001 in the course selection table above, you will get a direct error if you modify or delete this record in the course selection table at this time.

Cannot add or update a child row: a foreign key constraint fails (`mysql_grammar_test`.`sc_relation`, CONSTRAINT `sc_sid` FOREIGN KEY (`sid`) REFERENCES `students` (`sid`))

So we can use cascading to update or delete related data in multiple tables at the same time

Give the basic format first:

ALTER TABLE table name ADD CONSTRAINT foreign key name FOREIGN KEY REFERENCES main table name ON UPDATE CASCADE ON DELETE CASCADE;

A: Cascade update: ON UPDATE CASCADE
B: Cascade Delete: ON DELETE CASCADE

For example, test it

ALTER TABLE sc_relation ADD CONSTRAINT sc_sid FOREIGN KEY (sid) REFERENCES students(sid) ON UPDATE CASCADE ON DELETE CASCADE;

What we could not do before is now possible. For example, if we change 1001 number to 1008 in the student list, the related content in the selected timetable will change automatically according to the changes.

(3) End

If there are any deficiencies in the article, you are welcome to leave a message and exchange, thank your friends for their support!

If you can help, pay attention to me!If you prefer the way WeChat articles are read, you can focus on my public number

We don't know each other here, but we are all working for our dreamsInformation about

A public number insisting on original development technology articles: Ideal more than 20 days

Posted by gacon on Tue, 16 Jun 2020 18:12:15 -0700

Programmer Group