Sql 简明教程

SQL - Default Constraint

The SQL DEFAULT Constraint

SQL DEFAULT Constraint 用于指定表中的列的默认值。我们通常在创建表时设置默认值。

The SQL DEFAULT Constraint is used to specify the default value for a column of a table. We usually set default value while creating the table.

如果没有在插入数据时提供值，则默认值将被视为列值，从而确保始终为该列提供一个值。我们可以在 SQL 表中为多个列指定默认值。

The default values are treated as the column values if no values are provided while inserting the data, ensuring that the column will always have a value. We can specify default values for multiple columns in an SQL table.

Syntax

下面是 SQL DEFAULT 约束的语法 −

Following is the syntax of the SQL DEFAULT Constraint −

CREATE TABLE table_name (
   column1 datatype DEFAULT default_value,
   column2 datatype DEFAULT default_value,
   column3 datatype,
   .....
   columnN datatype
);

Example

在下面的查询中，我们使用 CREATE TABLE 语句来创建 CUSTOMERS 表。此处，我们正在向 NAME、AGE、ADDRESS 和 SALARY 列添加默认约束 −

In the following query we are creating the CUSTOMERS table using the CREATE TABLE statement. Here, we are adding a default constraint to the columns NAME, AGE, ADDRESS, and SALARY −

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL DEFAULT 'Ramesh',
   AGE INT NOT NULL DEFAULT '32',
   ADDRESS CHAR (25) DEFAULT 'Ahmedabad',
   SALARY DECIMAL (18, 2) DEFAULT '2000',
   PRIMARY KEY (ID)
);

以下查询使用 INSERT 语句将值插入此表：

Following query inserts values into this table using the INSERT statement −

INSERT INTO CUSTOMERS (ID) VALUES (1);
INSERT INTO CUSTOMERS VALUES (2, 'Khilan', 25, 'Delhi', 1500.00 );

该表使用 NAME、AGE、ADDRESS 和 SALARY 列中的默认值创建，第一行如下所示 −

The table is created with default values in the NAME, AGE, ADDRESS, and SALARY columns for the first row as shown below −

ID

NAME

AGE

ADDRESS

SALARY

1

Ramesh

32

Ahmedabad

2000.00

2

Khilan

25

Delhi

1500.00

Passing "DEFAULT" as Value

在将数据插入表时，如果列名未包含在 INSERT 查询中，为了将默认值插入记录中，我们需要传递“DEFAULT”作为值，如下所示 −

While inserting data into a table, if the column names are not included in the INSERT query, to insert the default value into the record we need to pass "DEFAULT" as a value, as shown below −

INSERT INTO CUSTOMERS VALUES
(3, 'Kaushik', DEFAULT, DEFAULT, 2000.00),
(4, 'Chaitali', DEFAULT, DEFAULT, DEFAULT);

获得的表如下所示：

The table obtained is as shown below −

ID

NAME

AGE

ADDRESS

SALARY

1

Ramesh

32

Ahmedabad

2000.00

2

Khilan

25

Delhi

1500.00

3

Kaushik

32

Ahmedabad

2000.00

4

Chaitali

32

Ahmedabad

2000.00

Adding Default Constraint to an Existing Column

我们还可以使用 ALTER TABLE 语句将默认约束添加到表的现有列。这允许我们通过指定默认值来修改现有表的结构，确保数据库中的数据一致性。

We can also add default constraints to an existing column of a table using the ALTER TABLE statement. This allows us to modify the structure of existing table by specifying default values, ensuring data consistency in the database.

Syntax

以下是向现有表中列添加 default constraint 的语法 −

Following is the syntax for adding a default constraint to a column in an existing table −

ALTER TABLE table_name
ALTER COLUMN column_name SET DEFAULT 'default_value';

Example

假设我们使用 CREATE TABLE 语句创建了另一个名为 BUYERS 的表，如下所示 −

Assume we have created another table named BUYERS using the CREATE TABLE statement as shown below −

CREATE TABLE BUYERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

以下查询向 BUYERS 表的 ADDRESS 列添加了默认约束。

Following query, we adds a default constraint to the ADDRESS column of the BUYERS table.

ALTER TABLE BUYERS ALTER ADDRESS SET DEFAULT 'Delhi';

以下 INSERT 语句通过向除了 ADDRESS 以外的所有列提供值，将记录插入 BUYERS 表。

Following INSERT statement inserts a record into the BUYERS table by providing values to all the columns except ADDRESS −

INSERT INTO BUYERS (ID, NAME, AGE, SALARY) VALUES
(01, 'Rahul', 27, 50000);

Verification

在插入记录后，如果您将其取回，则可以在地址列中观察到默认值（“德里的”） −

After inserting the record if you retrieve it back, you can observe the default value ("Delhi") in the address column −

SELECT * FROM BUYERS WHERE ID = 01;

Output

获得的表如下 −

The table obtained is as follows −

ID

NAME

AGE

ADDRESS

SALARY

01

Rahul

27

Delhi

50000.00

Dropping Default Constraint

我们可以使用 ALTER TABLE…​ DROP 语句从表中删除默认约束。

We can delete the default constraint from a table using the ALTER TABLE…​ DROP statement.

Syntax

以下是从表中删除默认约束的语法 −

Following is the syntax to delete the default constraint from a table −

ALTER TABLE table_name
ALTER COLUMN column_name DROP DEFAULT;

Example

在此处，我们正在从 CUSTOMERS 表的 ADDRESS 列中删除默认约束 −

In here, we are removing the default constraint from the ADDRESS column of the CUSTOMERS table −

ALTER TABLE CUSTOMERS ALTER ADDRESS DROP DEFAULT;

Verification

我们可以验证表详细信息（结构）并检查是否存在默认约束，可以使用以下查询 −

We can verify the table details (structure) and check whether there is a default constraint or not using the following query −

DESC CUSTOMERS;

获得的表如下所示：

The table obtained is as shown below −

Field

Type

Null

Key

Default

Extra

ID

int

NO

PRI

NULL

NAME

varchar(20)

NO

Ramesh

AGE

int

NO

32

ADDRESS

char(25)

YES

NULL

SALARY

decimal(18,2)

YES

2000.00