sql约束_SQL约束

sql约束

SQL Constraints are rules used to limit the type of data that can go into a table, to maintain the accuracy and integrity of the data inside table.

SQL约束是用于限制可以进入表的数据类型的规则，以保持表内数据的准确性和完整性。

Constraints can be divided into the following two types,

约束可以分为以下两种类型：

1. Column level constraints: Limits only column data.

   列级别约束：仅限制列数据。

2. Table level constraints: Limits whole table data.

   表级约束：限制整个表数据。

Constraints are used to make sure that the integrity of data is maintained in the database. Following are the most used constraints that can be applied to a table.

约束用于确保在数据库中维护数据的完整性。 以下是可以应用于表的最常用的约束。

• NOT NULL

  非空

• UNIQUE

  独特

• PRIMARY KEY

  首要的关键

• FOREIGN KEY

  外键

• CHECK

  检查

• DEFAULT

  默认

NOT NULL约束 (NOT NULL Constraint)

NOT NULL constraint restricts a column from having a NULL value. Once NOT NULL constraint is applied to a column, you cannot pass a null value to that column. It enforces a column to contain a proper value.

NOT NULL约束限制列具有NULL值。 一旦将NOT NULL约束应用于列，就无法将null值传递给该列。 它强制列包含正确的值。

One important point to note about this constraint is that it cannot be defined at table level.

关于此约束要注意的重要一点是，不能在表级别定义它。

使用NOT NULL约束的示例 (Example using NOT NULL constraint)

CREATE TABLE Student(s_id int NOT NULL, Name varchar(60), Age int);

The above query will declare that the s_id field of Student table will not take NULL value.

上面的查询将声明Student表的s_id字段不采用NULL值。

UNIQUE约束 (UNIQUE Constraint)

UNIQUE constraint ensures that a field or column will only have unique values. A UNIQUE constraint field will not have duplicate data. This constraint can be applied at column level or table level.

UNIQUE约束确保字段或列将仅具有唯一值。 UNIQUE约束字段将没有重复的数据。 可以在列级别或表级别应用此约束。

创建表(表级别)时使用UNIQUE约束 (Using UNIQUE constraint when creating a Table (Table Level))

Here we have a simple CREATE query to create a table, which will have a column s_id with unique values.

在这里，我们有一个简单的CREATE查询来创建一个表，该表将具有带有唯一值的s_id列。

CREATE TABLE Student(s_id int NOT NULL UNIQUE, Name varchar(60), Age int);

The above query will declare that the s_id field of Student table will only have unique values and wont take NULL value.

上面的查询将声明Student表的s_id字段仅具有唯一值，而不会采用NULL值。

创建表后使用UNIQUE约束(列级) (Using UNIQUE constraint after Table is created (Column Level))

ALTER TABLE Student ADD UNIQUE(s_id);

The above query specifies that s_id field of Student table will only have unique value.

上面的查询指定Student表的s_id字段仅具有唯一值。

主键约束 (Primary Key Constraint)

Primary key constraint uniquely identifies each record in a database. A Primary Key must contain unique value and it must not contain null value. Usually Primary Key is used to index the data inside the table.

主键约束唯一地标识数据库中的每个记录。 主键必须包含唯一值，并且不能包含空值。 通常，主键用于索引表中的数据。

在表级别使用PRIMARY KEY约束 (Using PRIMARY KEY constraint at Table Level)

CREATE table Student (s_id int PRIMARY KEY, Name varchar(60) NOT NULL, Age int);

The above command will creates a PRIMARY KEY on the s_id.

上面的命令将在s_id上创建一个PRIMARY KEY。

在列级别使用PRIMARY KEY约束 (Using PRIMARY KEY constraint at Column Level)

ALTER table Student ADD PRIMARY KEY (s_id);

The above command will creates a PRIMARY KEY on the s_id.

上面的命令将在s_id上创建一个PRIMARY KEY。

外键约束 (Foreign Key Constraint)

FOREIGN KEY is used to relate two tables. FOREIGN KEY constraint is also used to restrict actions that would destroy links between tables. To understand FOREIGN KEY, let's see its use, with help of the below tables:

FOREIGN KEY用于关联两个表。 FOREIGN KEY约束还用于限制可能破坏表之间链接的操作。 要了解FOREIGN KEY，请在下表的帮助下查看其用法：

Customer_Detail Table

客户明细表

c_id	Customer_Name	address
101	Adam	Noida
102	Alex	Delhi
103	Stuart	Rohtak

c_id	顾客姓名	地址
101	亚当	野田
102	亚历克斯	新德里
103	斯图尔特	罗塔克

Order_Detail Table

订单明细表

Order_id	Order_Name	c_id
10	Order1	101
11	Order2	103
12	Order3	102

Order_id	订单名称	c_id
10	订单1	101
11	Order2	103
12	订单3	102

In Customer_Detail table, c_id is the primary key which is set as foreign key in Order_Detail table. The value that is entered in c_id which is set as foreign key in Order_Detail table must be present in Customer_Detail table where it is set as primary key. This prevents invalid data to be inserted into c_id column of Order_Detail table.

在Customer_Detail表中， c_id是在Order_Detail表中设置为外键的主键。 在c_id中输入的值(在Order_Detail表中设置为外键)必须存在于Customer_Detail表中，在该值中将其设置为主键。 这防止无效数据被插入到Order_Detail表的C_ID列。

If you try to insert any incorrect data, DBMS will return error and will not allow you to insert the data.

如果您尝试插入任何不正确的数据，DBMS将返回错误，并且不允许您插入数据。

在表级别使用FOREIGN KEY约束 (Using FOREIGN KEY constraint at Table Level)

CREATE table Order_Detail(
    order_id int PRIMARY KEY, 
    order_name varchar(60) NOT NULL,
    c_id int FOREIGN KEY REFERENCES Customer_Detail(c_id)
);

In this query, c_id in table Order_Detail is made as foriegn key, which is a reference of c_id column in Customer_Detail table.

在此查询中，将表Order_Detail中的c_id用作前键，这是Customer_Detail表中c_id列的引用。

在列级别使用FOREIGN KEY约束 (Using FOREIGN KEY constraint at Column Level)

ALTER table Order_Detail ADD FOREIGN KEY (c_id) REFERENCES Customer_Detail(c_id);

Foriegn键列在删除时的行为 (Behaviour of Foriegn Key Column on Delete)

There are two ways to maintin the integrity of data in Child table, when a particular record is deleted in the main table. When two tables are connected with Foriegn key, and certain data in the main table is deleted, for which a record exits in the child table, then we must have some mechanism to save the integrity of data in the child table.

当在主表中删除特定记录时，有两种方法可以维护子表中数据的完整性。 当用Foriegn键将两个表连接在一起，并且删除了主表中的某些数据(在子表中为此记录退出)时，我们必须具有某种机制来保存子表中数据的完整性。

1. On Delete Cascade : This will remove the record from child table, if that value of foriegn key is deleted from the main table.

   在Delete Cascade上：如果从主表中删除了foreign键的值，则将从子表中删除该记录。

2. On Delete Null : This will set all the values in that record of child table as NULL, for which the value of foriegn key is deleted from the main table.

   On Delete Null：这会将子表的该记录中的所有值设置为NULL，为此，将从主表中删除foriegn键的值。

3. If we don't use any of the above, then we cannot delete data from the main table for which data in child table exists. We will get an error if we try to do so.

   如果我们不使用上述任何方法，那么我们将无法从存在子表中数据的主表中删除数据。 如果尝试这样做，将会得到一个错误。

4. ERROR : Record in child table exist
   

CHECK约束 (CHECK Constraint)

CHECK constraint is used to restrict the value of a column between a range. It performs check on the values, before storing them into the database. Its like condition checking before saving data into a column.

CHECK约束用于将列的值限制在一个范围之间。 在将值存储到数据库中之前，它将执行检查。 类似于在将数据保存到列之前进行条件检查。

在表级别使用CHECK约束 (Using CHECK constraint at Table Level)

CREATE table Student(
    s_id int NOT NULL CHECK(s_id > 0),
    Name varchar(60) NOT NULL,
    Age int
);

The above query will restrict the s_id value to be greater than zero.

上面的查询将s_id值限制为大于零。

在列级别使用CHECK约束 (Using CHECK constraint at Column Level)

ALTER table Student ADD CHECK(s_id > 0);

翻译自: https://www.studytonight.com/dbms/sql-constraints.php

sql约束