altering DEFAULT constraint on column SQL

Question

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 says the follow code should be able to drop the DEFAULT like this

ALTER TABLE Persons
ALTER COLUMN City DROP DEFAULT

but i get a syntax error on DEFAULT

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

Answer 1

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

Then you can drop it using:

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

Answer 2

The syntax is:

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

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