更改 SQL 列上的 DEFAULT 约束 [英] altering DEFAULT constraint on column SQL

问题描述

我有一个用于创建表的 SQL 脚本，我希望除少数列之外的所有列的默认值都为"，其他列的默认值为 0

I have an SQL script for creating a table, i would like the default of all but a few columns to be "" the others require a integer default of 0

以下创建表格.删除了一些列，因为有很多

The following creates the table. Some columns are removed because there are lots

CREATE TABLE [dbo].[PMIPatients]
(
[PID] [varchar](30) NOT NULL,
[PatientFirstName] [varchar](30) NULL,
[PatientLastName] [varchar](30) NULL,
[PatientDOB] [varchar](30) NULL,
[PatientDoctor] [varchar](30) NULL,
[PatientDiet] [varchar](50) NULL,
[PatientFallRiskLevel] [int] NULL,
[BedId] [int] NULL,
[BedDisplayInfo] TEXT NOT NULL DEFAULT ''
CONSTRAINT [PK_HL7Patient] PRIMARY KEY CLUSTERED 
([PID] ASC) WITH (PAD_INDEX  = OFF,
    STATISTICS_NORECOMPUTE  = OFF,
    IGNORE_DUP_KEY = OFF, 
    ALLOW_ROW_LOCKS  = ON,
    ALLOW_PAGE_LOCKS  = ON)
ON [PRIMARY]
) 
ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

我希望在选定的列上设置不同的默认值，以下代码不起作用，因为它表示已经设置了默认约束.所以我假设我必须先放弃约束.

I wish to set a different default on selected columns, the following code does not work as it says that there is already a default constraint set. So i assumne i have to drop the constraint first.

ALTER TABLE [dbo].[PMIPatients] ADD  
DEFAULT ((0)) 
FOR [PatientFallRiskLevel]

http://www.w3schools.com/sql/sql_default.asp 说以下代码应该能够像这样删除 DEFAULT

http://www.w3schools.com/sql/sql_default.asp says the follow code should be able to drop the DEFAULT like this

ALTER TABLE Persons
ALTER COLUMN City DROP DEFAULT

但我在 DEFAULT 上遇到语法错误

but i get a syntax error on DEFAULT

如何更改/删除特定列的 DEFAULT 约束

How do i alter/drop the DEFAULT constraint of specific columns

推荐答案

添加默认值时，应使用名称作为约束.这样您以后就可以按名称引用这些约束.

When you add a default, you should use names for your constraints. This way you can later refer to those constraints by name.

ALTER TABLE [dbo].[PMIPatients] ADD CONSTRAINT [PatientFallRiskLevel_Default] DEFAULT ((0)) FOR PatientFallRiskLevel

然后你可以使用:

ALTER TABLE [dbo].[PMIPatients] DROP CONSTRAINT [PatientFallRiskLevel_Default] 

这篇关于更改 SQL 列上的 DEFAULT 约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！