altering DEFAULT constraint on column SQL Server
sql-server, t-sql
Solution
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]
Problem
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