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:
    列级别约束：仅限制列数据。
  2. Table level constraints:
    表级约束：限制整个表数据。

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

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

Student表的s_id字段不采用NULL值。

UNIQUE约束 (UNIQUE Constraint)

UNIQUE constraint ensures that a field or column will only have unique values. A UNIQUE

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

在这里，我们有一个简单的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

Student表的s_id字段仅具有唯一值，而不会采用NULL值。

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

ALTER TABLE Student ADD UNIQUE(s_id);

s_id field of Student

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.

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

(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。

(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

客户明细表

c_id

Customer_Name

address

101

Adam

Noida

102

Alex

Delhi

103

Stuart

Rohtak

c_id

顾客姓名

地址

101

亚当

野田

102

亚历克斯

新德里

103

斯图尔特

罗塔克

Order_Detail

订单明细表

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

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

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将返回错误，并且不允许您插入数据。

(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

c_id用作前键，这是Customer_Detail表中c_id列的引用。

(Using FOREIGN KEY constraint at Column Level)

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

(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键将两个表连接在一起，并且删除了主表中的某些数据(在子表中为此记录退出)时，我们必须具有某种机制来保存子表中数据的完整性。




java限制sql查询数量_java限制sql查询数量


  1. On Delete Cascade :
    在Delete Cascade上：如果从主表中删除了foreign键的值，则将从子表中删除该记录。
  2. On Delete Null :
    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.
    如果我们不使用上述任何方法，那么我们将无法从存在子表中数据的主表中删除数据。 如果尝试这样做，将会得到一个错误。 
ERROR : Record in child table exist

CHECK约束 (CHECK Constraint)

CHECK

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

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约束