sql约束

In a real-world scenario, there are cases when we would like to restrict the type of data that is getting stored in the DB table. In order to achieve the restriction on the database, SQL provides a set of constraints.

在现实世界中，有时候我们想限制存储在数据库表中的数据类型。 为了实现对数据库的限制，SQL提供了一组约束。

介绍 (Introduction)

SQL constraints are a set of rules that are used for restricting the data that will be inserted in the database table. There are two types of constraints.

SQL约束是一组规则，用于限制将要插入数据库表中的数据。 有两种类型的约束。

1. Table-level constraints.
   表级约束。
2. Column level constraints.
   列级约束。

The column based constrains is applicable only to one column whereas the table level constraint is applicable for the complete table.

基于列的约束仅适用于一列，而表级约束则适用于整个表。

Please find below the list of constraints.

请在约束列表下方找到。

1. NOT NULL
   非空
2. UNIQUE
   独特
3. PRIMARY KEY
   首要的关键
4. FOREIGN KEY
   外键
5. CHECK
   检查
6. DEFAULT
   默认

Let’s discuss all the above-mentioned constraints in detail.

让我们详细讨论所有上述约束。

1. NOT NULL约束 (1. NOT NULL Constraint)

SQL NOT NULL constraint specifies that the column can not contains any null value for the column. The constraint can be specified during table or .

SQL NOT NULL约束指定该列不能包含该列的任何空值。 可以在表或期间指定约束。

Syntax: –

句法： -

CREATE TABLE 
    
     (
      
      
        NOT NULL);
      
     
    

In the syntax above, a NOT NULL keyword is used to specify that the column can not accept a null value.

在上面的语法中，NOT NULL关键字用于指定列不能接受空值。

Let’s create a customer table to understand this better.

让我们创建一个客户表来更好地理解这一点。

CREATE TABLE customer(cust_id varchar(8) NOT NULL UNIQUE, cust_name varchar(50) NOT NULL, state varchar(25), country varchar(25) );

Post-execution of the above query at MySQL workbench. The following screen will appear.

在MySQL工作台上执行以上查询。 将出现以下屏幕。

On performing a table inspection following screen will appear.

进行桌子检查时，将出现以下屏幕。

The highlighted value shows that “No” values mean that the column will not accept a null value.

突出显示的值表明“否”值表示该列将不接受空值。

2.唯一约束 (2. UNIQUE Constraint)

UNIQUE specifies that the column can not duplicate value for the column. The constraint can be specified during table or .

UNIQUE指定该列不能重复该列的值。 可以在表或期间指定约束。

Syntax: –

句法： -

CREATE TABLE 
    
     (
      
      
        UNIQUE);
      
     
    

In the syntax above, a UNIQUE keyword is used to specify that the column can not accept a duplicate value.

在上面的语法中，UNIQUE关键字用于指定该列不能接受重复值。

Let’s create a customer table to understand this better.

让我们创建一个客户表来更好地理解这一点。

CREATE TABLE customer(cust_id varchar(8) NOT NULL UNIQUE, cust_name varchar(50) NOT NULL, state varchar(25), country varchar(25) );

Post-execution of the above query at MySQL workbench. The following screen will appear.

在MySQL工作台上执行以上查询。 将出现以下屏幕。

On performing a table inspection following screen will appear.

进行桌子检查时，将出现以下屏幕。

The value for Unique column is “YES” which mean that the column will accept a unique value.

“唯一”列的值为“ YES”，表示该列将接受唯一值。

3.主键约束 (3. PRIMARY KEY Constraint)

In order to uniquely identify a row in a table, during table creation we specify a column or combination of columns as a primary key. The primary key should always be not null. Only one primary key can be specified for a table.

为了唯一地标识表中的行，在表创建期间，我们指定一列或列的组合作为主键。 主键应始终不为null。 一张表只能指定一个主键。

Syntax: –

句法： -

CREATE TABLE 
    
     (
      
      
        NOT NULL,
        
        
          NOT NULL,
          
          
            NOT NULL,PRIMARY KEY (column_name1));
          
         
        
       
      
     
    

In the syntax above, a PRIMARY KEY keyword is used to specify that the column will be used a the primary key for the table.

在上面的语法中，PRIMARY KEY关键字用于指定该列将用作表的主键。

Let’s create a customer table to understand this better.

让我们创建一个客户表来更好地理解这一点。

CREATE TABLE customer(cust_id varchar(8) NOT NULL UNIQUE, cust_name varchar(50) NOT NULL, state varchar(25), country varchar(25),PRIMARY KEY (cust_id) );

Post-execution of the above query at MySQL workbench. The following screen will appear.

在MySQL工作台上执行以上查询。 将出现以下屏幕。

On performing a table inspection following screen will appear.

进行桌子检查时，将出现以下屏幕。

The value for Unique column is “YES” which mean that the column will accept a unique value. Also, the key column has row value “PRIMARY” and the row value for columns column specifies the column that will be the primary key.

“唯一”列的值为“ YES”，表示该列将接受唯一值。 此外，键列的行值为“ PRIMARY”，而列的行值指定要作为主键的列。

4.外键约束 (4. FOREIGN KEY Constraint)

In order to have a connection of a row in a table with another table, during table creation, we specify a column or combination of columns as a foreign key. The foreign key should always be not null.

为了使一个表中的行与另一个表具有连接，在表创建期间，我们指定一个列或列的组合作为外键。 外键应始终不为null。

Syntax: –

句法： -

FOREIGN KEY [column list] REFERENCES [primary key table] ([column list]);

In the syntax above, a FOREIGN KEY keyword is used to specify the column list on which foreign key needs to be set. The primary key table is the table from which the foreign key will be used.

在上面的语法中，FOREIGN KEY关键字用于指定需要在其上设置外键的列列表。 主键表是将使用外键的表。

Let’s create a customer table to understand this better.

让我们创建一个客户表来更好地理解这一点。

CREATE TABLE customer(cust_id varchar(8) NOT NULL UNIQUE, cust_name varchar(50) NOT NULL, state varchar(25), country varchar(25),PRIMARY KEY (cust_id) );CREATE TABLE new_customer(cust_id varchar(8) NOT NULL UNIQUE,old_id varchar(8),cust_name varchar(50) NOT NULL, state varchar(25), country varchar(25),FOREIGN KEY (old_id) references customer(cust_id));

Post-execution of the above query at MySQL workbench. The following screen will appear.

在MySQL工作台上执行以上查询。 将出现以下屏幕。

On performing a table inspection following screen will appear.

进行桌子检查时，将出现以下屏幕。

The foreign key tab provides the details of the foreign keys.

外键选项卡提供了外键的详细信息。

5.检查约束 (5. CHECK Constraint)

There are cases when you would like to restrict the value that will be stored in the column.

在某些情况下，您想限制将存储在列中的值。

Syntax: –

句法： -

CREATE TABLE 
    
     (
      
      
        CHECK (range for check));
      
     
    

In the syntax above, the keyword CHECK is used for specifying a range.

在以上语法中，关键字CHECK用于指定范围。

Let’s create a table customer to understand this better.

让我们创建一个表客户来更好地理解这一点。

CREATE TABLE customer(cust_id varchar(8) NOT NULL UNIQUE, cust_name varchar(50) NOT NULL, phone decimal (10) check (phone=10),state varchar(25), country varchar(25),PRIMARY KEY (cust_id) );

Post-execution of the above query at MySQL workbench. When data insertion is done the constraint is checked for the phone column.

在MySQL工作台上执行以上查询。 完成数据插入后，将检查电话列的约束。

6.默认约束 (6. DEFAULT Constraint)

Using the DEFAULT keyword we can specify a default value for the column.

使用DEFAULT关键字，我们可以为列指定默认值。

Syntax: –

句法： -

CREATE TABLE 
    
     (
      
      
        DEFAULT 'value');
      
     
    

In the syntax above, the keyword DEFAULT is used for providing a default value.

在以上语法中，关键字DEFAULT用于提供默认值。

Let’s create a table customer to understand this better.

让我们创建一个表客户来更好地理解这一点。

CREATE TABLE customer(cust_id varchar(8) NOT NULL UNIQUE, cust_name varchar(50) NOT NULL, phone decimal (10) check (phone=10),state varchar(25), country varchar(25) DEFAULT 'India',PRIMARY KEY (cust_id) );

Post-execution of the above query at MySQL workbench. When data insertion is done, if any value is not provided it will consider ‘India’ as the value.

在MySQL工作台上执行以上查询。 数据插入完成后，如果未提供任何值，则会将“印度”视为该值。

翻译自:

sql约束