Zero foundation of MySQL from introduction to mastery (constraint)

MySQL constraints

concept

constraint
Constraints are actually constraints on the data in the table

effect

The purpose of adding constraints is to ensure the integrity and effectiveness of the records in the table. For example, the values of the column (mobile phone number) can not be empty, and the values of some columns (ID number) can not be repeated.

classification

• Primary key constraint
• Self growth constraint (auto_increment)
• Non NULL constraint (not null)
• Uniqueness constraint
• Default constraint (default)
• Zero fill constraint
• Foreign key constraint fk

Primary key constraint

concept

• MySQL primary key constraint is a column or a combination of multiple columns. Its value can uniquely identify each row in the table, which is convenient to find a row in RDBMS as soon as possible.
• The primary key constraint is equivalent to the combination of unique constraint and non empty constraint. The primary key constraint column cannot be duplicate or null. (not empty and unique)
• Only one primary key is allowed per table
• The key of the primary key constraint is: primary key
• When you create a constraint for a primary key, the system will establish a corresponding unique index on the column and column combination by default.

operation

• Add single column primary key
• Add multi column federated primary key
• Delete primary key

Add single column primary key

There are two ways to create a single column primary key: one is to specify the primary key while defining the field, and the other is to specify the primary key after defining the field.

Method 1: in the create table statement, specify the PRIMARY KEY through the PRIMARY KEY keyword.

Specify the primary key while defining the field

Syntax:

create table Table name(
   ...
   <Field name> <data type> primary key 
   ...
)

Example:

CREATE TABLE IF NOT EXISTS emp1(
	eid INT PRIMARY KEY,	-- appoint eid Column as single column primary key
	`name` VARCHAR(20),
	deptId INT,
	salary DOUBLE
);

In Navicat, you can view the following through the design table:
[the external chain image transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-IbeJE0sL-1640790399749)(E:\JavaEEWeb\day14_Mysql advanced \ sorting \ assets40787952919.png)]

Method 2: specify the primary key after defining the field.

Syntax:

create table Table name(
   ...
   [constraint <Constraint name>] primary key [Field name]
);

Example:

CREATE TABLE IF NOT EXISTS emp2(
	eid INT,
	`name` VARCHAR(20),
	deptId INT ,
	salary DOUBLE,
	CONSTRAINT pk1 PRIMARY KEY (eid)		-- CONSTRAINT pk1 Can save
);

Add multi column primary key (Federated primary key)

The so-called joint primary key means that the primary key is composed of multiple fields in a table.
be careful:

1. When a primary key consists of multiple fields, you cannot declare a primary key constraint directly after the field name.

2. A table can only have one primary key, and the union primary key is also a primary key

3. Joint primary key means that multiple columns are combined as a primary key. None of these columns can be empty and cannot be repeated.

Syntax:

create table Table name(
   ...
   primary key (Field 1, field 2,field n)
);

Example:

-- A federated primary key is a combination of multiple fields in a table
-- PRIMARY KEY(Field name 1,Field name 2,Field name 3,...,field n)
CREATE TABLE IF NOT EXISTS emp3(
`name` VARCHAR(20),
deptId INT,
salary DOUBLE,
CONSTRAINT pk2 PRIMARY KEY (name,deptId)	-- take name Column sum deptId Columns to create a federated primary key
);

In Navicat, you can view the following through the design table:

Adding a primary key by modifying the table structure

Primary key constraints can be created not only when the table is created, but also when the table is modified.

Syntax:

create table Table name(
   ...
);
alter table <Table name> add primary key(Field list);

Example:

-- Create table
CREATE TABLE IF NOT EXISTS emp4(
eid INT,
`name` VARCHAR(20),
deptId INT,
salary DOUBLE
);

-- Modify the table structure and add a single column primary key
ALTER TABLE emp4 add PRIMARY KEY(eid);

-- Create table
CREATE TABLE IF NOT EXISTS emp5(
eid INT,
`name` VARCHAR(20),
deptId INT,
salary DOUBLE
);

-- Modify table structure,Add federated primary key
ALTER TABLE emp5 add PRIMARY KEY (name,deptId);

Delete primary key constraint

When a primary key constraint is not required in a table, it needs to be deleted from the table. Deleting a primary key constraint is much easier than creating a primary key constraint.

When deleting a primary key constraint, you do not need to specify which primary key to delete, nor do you need to specify a single column primary key or a joint primary key, because each table has only one primary key.

Syntax:

alter table Data table name drop primary key;

Example:

-- Delete primary key
-- Delete single column primary key
ALTER TABLE emp1 DROP PRIMARY KEY;

-- Delete multiple primary keys
ALTER TABLE emp5 DROP PRIMARY KEY;