MySQL约束

2024年03月27日 MySQL约束 极客笔记

MySQL约束

MySQL中的约束用于指定允许或限制存储在表中的值/数据的规则。它们提供了一种适当的方法来确保表中数据的准确性和完整性。它还有助于限制将插入表中的数据类型。如果约束和数据操作之间发生任何中断，操作将失败。

MySQL约束的类型

MySQL中的约束分为两种类型：

  1. 列级约束： 这些约束仅适用于限制特定列数据类型的单个列。
  2. 表级约束： 这些约束适用于整个表，限制整个表的数据类型。

如何在MySQL中创建约束

我们可以在创建表时使用CREATE TABLE语句定义约束。在现有表模式的情况下，MySQL还使用ALTER TABLE语句指定约束。

语法

以下是在表中创建约束的语法：

CREATE TABLE new_table_name (
    col_name1 datatype constraint,
    col_name2 datatype constraint,
    col_name3 datatype constraint,
    .........
);

MySQL中使用的约束

以下是MySQL中常用的约束：

  • NOT NULL
  • CHECK
  • DEFAULT
  • PRIMARY KEY
  • AUTO_INCREMENT
  • UNIQUE
  • INDEX
  • ENUM
  • FOREIGN KEY

让我们详细讨论每个约束。

NOT NULL约束

该约束指定列不能有NULL值或空值。以下语句创建了一个带有NOT NULL约束的表。

mysql> CREATE TABLE Student(Id INTEGER, LastName TEXT NOT NULL, FirstName TEXT NOT NULL, City VARCHAR(35));

执行下面列出的查询以了解它是如何工作的：

mysql> INSERT INTO Student VALUES(1, 'Hanks', 'Peter', 'New York');

mysql> INSERT INTO Student VALUES(2, NULL, 'Amanda', 'Florida');

输出

在上面的图中，我们可以看到第一个INSERT查询执行正确，但是第二个语句失败并报错，显示列LastName不能为空。

唯一约束

该约束确保插入到列中的所有值都是唯一的。这意味着列不能存储重复的值。MySQL允许我们在表中使用多列的唯一约束。下面的语句创建一个带有唯一约束的表:

mysql> CREATE TABLE ShirtBrands(Id INTEGER, BrandName VARCHAR(40) UNIQUE, Size VARCHAR(30));

执行下列查询以了解其工作原理:

mysql> INSERT INTO ShirtBrands(Id, BrandName, Size) VALUES(1, 'Pantaloons', 38), (2, 'Cantabil', 40);

mysql> INSERT INTO ShirtBrands(Id, BrandName, Size) VALUES(1, 'Raymond', 38), (2, 'Cantabil', 40);

输出

在下面的输出中，我们可以看到第一个INSERT查询执行正确，但是第二个语句失败，并且给出了一个错误，错误消息为：BrandName键的值’Cantabil’重复。

CHECK约束

它控制了特定列中的值。它确保插入到列中的值必须符合给定的条件。换句话说，它确定与列相关联的值是否与给定条件有效。

在8.0.16版本之前，MySQL使用了此约束语法的有限版本，如下所示：

CHECK (expr)

在8.0.16版本之后，MySQL对所有存储引擎都使用CHECK约束，即表约束和列约束，如下所示：

[CONSTRAINT [symbol]] CHECK (expr) [[NOT] ENFORCED]

让我们了解一下MySQL中的CHECK约束的工作原理。例如，以下语句创建了一个名为”Persons”的表，该表在”Age”列上包含CHECK约束。CHECK约束确保插入到列中的值必须满足给定的条件，即一个人的年龄必须大于或等于18岁：

mysql> CREATE TABLE Persons (
    ID int NOT NULL,
    Name varchar(45) NOT NULL,
    Age int CHECK (Age>=18)
);

执行以下查询将值插入表中：

mysql> INSERT INTO Persons(Id, Name, Age) 
VALUES (1,'Robert', 28), (2, 'Joseph', 35), (3, 'Peter', 40);

mysql> INSERT INTO Persons(Id, Name, Age) VALUES (1,'Robert', 15);

输出

在下面的输出中，我们可以看到第一个INSERT查询成功执行，但第二个语句失败并显示了一个错误，提示说：键Age违反了CHECK约束。

默认约束

此约束用于为未指定任何值的特定列设置默认值。这意味着该列必须包含一个值，包括NULL。

例如，下面的语句创建了一个名为”Persons”的表，该表在”City”列上包含了DEFAULT约束。如果我们没有为City列指定任何值，它会插入默认值：

mysql> CREATE TABLE Persons (
    ID int NOT NULL,
    Name varchar(45) NOT NULL,
    Age int,
    City varchar(25) DEFAULT 'New York'
);

执行以下查询将值插入到表中：

mysql> INSERT INTO Persons(Id, Name, Age, City) 
VALUES (1,'Robert', 15, 'Florida'), 
(2, 'Joseph', 35, 'California'), 
(3, 'Peter', 40, 'Alaska');

mysql> INSERT INTO Persons(Id, Name, Age) VALUES (1,'Brayan', 15);

输出

在下面的输出中，我们可以看到第一个包含所有字段的插入查询成功执行，而第二个插入语句没有包含“城市”列但仍成功执行。这是因为它有一个默认值。

现在，执行以下语句验证第4列的默认值：

mysql> SELECT * FROM Persons;

我们可以看到它完美地运作。这意味着默认值”New York”会自动存储在City列中。

主键约束

此约束用于唯一标识表中的每个记录。如果列包含主键约束，则不能为null或为空。一个表可以有重复的列，但只能包含一个主键。它始终在列中包含唯一的值。

以下语句创建一个名为”Person”的表，并更清楚地解释了主键的用法：

CREATE TABLE Persons (
    ID int NOT NULL PRIMARY KEY, 
    Name varchar(45) NOT NULL, 
    Age int, 
    City varchar(25));

接下来，使用insert查询将数据存储到表中：

INSERT INTO Persons(Id, Name, Age, City) 
VALUES (1,'Robert', 15, 'Florida') , 
(2, 'Joseph', 35, 'California'), 
(3, 'Peter', 40, 'Alaska');

INSERT INTO Persons(Id, Name, Age, City) 
VALUES (1,'Stephen', 15, 'Florida');

输出

在下面的输出中，我们可以看到第一条插入查询成功执行。而第二个插入语句失败并显示一个错误，错误信息为：主键列存在重复条目。

AUTO_INCREMENT约束

该约束在每次向表中插入新记录时自动生成一个唯一的数字。通常，我们将该约束用于表中的主键字段。

我们可以通过以下示例来理解，在Animal表中，id列将会自动递增：

mysql> CREATE TABLE Animals(
id int NOT NULL AUTO_INCREMENT, 
name CHAR(30) NOT NULL, 
PRIMARY KEY (id));

接下来，我们需要将值插入到”Animals”表中：

mysql> INSERT INTO Animals (name) VALUES 
('Tiger'),('Dog'),('Penguin'), 
('Camel'),('Cat'),('Ostrich');

现在，执行以下语句以获取表数据:

mysql> SELECT * FROM Animals;

输出

在输出中，我们可以看到我没有为自增列指定任何值，所以MySQL会自动为该字段生成一个唯一的数字，按照顺序排列。

ENUM约束

MySQL中的ENUM数据类型是一个字符串对象。它允许我们在表创建时通过列规范来限制从允许值列表中选择的值。它是枚举的简称，意味着每个列可以具有指定的可能值之一。它使用数字索引(1, 2, 3…)来表示字符串值。

以下示例创建了一个名为”shirts”的表，包含三个列：id、name和size。列名”size”使用了包含small、medium、large和x-large尺寸的ENUM数据类型。

mysql> CREATE TABLE Shirts (  
    id INT PRIMARY KEY AUTO_INCREMENT,   
    name VARCHAR(35),   
    size ENUM('small', 'medium', 'large', 'x-large')  
);

接下来，我们需要使用以下语句将值插入到“Shirts”表中：

mysql> INSERT INTO Shirts(id, name, size)   
VALUES (1,'t-shirt', 'medium'),   
(2, 'casual-shirt', 'small'),   
(3, 'formal-shirt', 'large');

现在，执行SELECT语句以查看插入到表中的值：

mysql> SELECT * FROM Shirts;

输出

我们将得到以下输出：

INDEX约束

这个约束允许我们非常快速和轻松地创建和检索表中的值。可以使用一个或多个列来创建索引。它为表中的每一行分配一个ROWID，以记录它们被插入到表中的方式。

下面的示例创建了一个名为”shirts”的表，包含三个列：id、name和size。

mysql> CREATE TABLE Shirts (  
    id INT PRIMARY KEY AUTO_INCREMENT,   
    name VARCHAR(35),   
    size ENUM('small', 'medium', 'large', 'x-large')  
);

接下来，我们需要使用下面的语句将值插入到“Shirts”表中：

mysql> INSERT INTO Shirts(id, name, size)   
VALUES (1,'t-shirt', 'medium'),   
(2, 'casual-shirt', 'small'),   
(3, 'formal-shirt', 'large');

现在，执行以下语句来创建索引：

mysql> CREATE INDEX idx_name ON Shirts(name);

我们可以使用以下查询通过索引列来检索数据:

mysql> SELECT * FROM Shirts USE INDEX(idx_name);

输出

出现以下输出：

外键约束

该约束用于将两个表进行关联。它也被称为引用键。外键列与另一个表的主键字段相匹配。这意味着一个表中的外键字段引用另一个表的主键字段。

让我们考虑这些表的结构: Persons 和 Orders。

表：Persons

CREATE TABLE Persons (
    Person_ID int NOT NULL PRIMARY KEY, 
    Name varchar(45) NOT NULL, 
    Age int, 
    City varchar(25)
);

表：订单

CREATE TABLE Orders (
    Order_ID int NOT NULL PRIMARY KEY,
    Order_Num int NOT NULL,
    Person_ID int,
    FOREIGN KEY (Person_ID) REFERENCES Persons(Person_ID)
);

在上面的表结构中，我们可以看到”Orders”表中的”Person_ID”字段指向”Persons”表中的”Person_ID”字段。”Person_ID”是”Persons”表中的主键，而”Orders”表的”Person_ID”列是一个外键。

输出结果

我们的表包含以下数据：

本文链接：http://so.lmcjl.com/news/531/

展开阅读全文
上一篇：MySQL 查询 下一篇：Python json模块：处理json文件
相关内容
1.mysql保存数据位置
2.MySQL error什么意思
3.MySQL字段值长度的函数
4.MySQL any_value 函数详解
5.MySQL text 字段存储问题
6.MySQL中的NOT IN用法详解
7.MAMP中MySQL不亮解决方法
8.MySQL的TEXT数据类型支持多少字符
9.dpkg错误处理中的mysql-server依赖问题