Technology Sharing

한어Русский языкEnglishFrançaisIndonesianSanskrit日本語DeutschPortuguêsΕλληνικάespañolItalianoSuomalainenLatina

SQL Constraints

1. Description

Concept: Constraints are rules that act on fields in a table and are used to limit the data stored in the table.

Purpose: To ensure the correctness, validity and integrity of data in the database.

2. Usage

Auto-increment: auto_increment

MySQL

ALTER TABLE my_table MODIFY id INT AUTO_INCREMENT;
1

SQLServer

-- 删除依赖于id列的约束
ALTER TABLE my_table DROP CONSTRAINT <constraint_name>;

-- 修改字段为自增
ALTER TABLE my_table ALTER COLUMN id INT IDENTITY(1,1);

-- 重新创建约束（如果有）
ALTER TABLE my_table ADD CONSTRAINT <constraint_name> PRIMARY KEY (id);
1
2
3
4
5
6
7
8

Adding constraints

-- 1、添加主键
ALTER TABLE 表名 ADD CONSTRAINT 主键名 PRIMARY KEY (主键字段名);
-- 例：ALTER TABLE Orders ADD CONSTRAINT FK_Orders_Customers FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID);
-- 2、添加外键
alter table 表名 add constraint 外键名 foreign key (外键字段名) references 主表(主字段名);
-- 删除外键
alter table 表名 drop foreign key 外键名;
-- 3、添加唯一约束
ALTER TABLE 表名 ADD CONSTRAINT 约束名 (需要约束的字段);
-- 4、添加默认约束
ALTER TABLE 表名 ADD CONSTRAINT 约束名 DEFAULT '默认值' FOR 字段名;
-- 5、检查约束
ALTER TABLE 表名 ADD CONSTRAINT 约束名 CHECK (Age >= 18 检查的条件);
-- 6、非空约束
ALTER TABLE 表名 ALTER COLUMN 字段名 varchar(255) NOT NULL;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

The role of foreign keys：
To ensure data integrity, the table with foreign key fields is called the child table, and the associated table is called the parent table.
Deletion and update behavior：

• no action (default): When deleting/updating the corresponding record in the parent table, if the record has a foreign key,It cannot be updated/deleted.
• Cascade: When deleting/updating the corresponding record in the parent table, if the record has a foreign key,Then delete/update the records in the child table.
• set null: When deleting/updating the corresponding record in the parent table, if the record has a foreign key,Then set the foreign key value in the child table to null.
• set default: When the parent table is changed, the foreign key column in the child table is set to a default value (not supported by InnoDB).

# 指定行为
alter table 表名 add constraint 外键名 foreign key (外键字段) references 主表(主字段名) on update cascade on delete cascade;
1
2