title: altering DEFAULT constraint on column SQL Server
body: <p>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 </p>
  
  <p>The following creates the table. Some columns are removed because there are lots</p>
  
  <pre><code>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]
  </code></pre>
  
  <p>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. </p>
  
  <pre><code>ALTER TABLE [dbo].[PMIPatients] ADD  
  DEFAULT ((0)) 
  FOR [PatientFallRiskLevel]
  </code></pre>
  
  <p><a href="http://www.w3schools.com/sql/sql_default.asp" rel="noreferrer">http://www.w3schools.com/sql/sql_default.asp</a> says the follow code should be able to drop the DEFAULT like this</p>
  
  <pre><code>ALTER TABLE Persons
  ALTER COLUMN City DROP DEFAULT
  </code></pre>
  
  <p>but i get a syntax error on DEFAULT</p>
  
  <p>How do i alter/drop the DEFAULT constraint of specific columns </p>