altering DEFAULT constraint on column SQL Server

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

Just don't use w3school, use MSDN instead for first-hand information. — Szymon, Dec 03 '13 at 23:20
Possible duplicate of [Modify Default value in SQL Server](http://stackoverflow.com/questions/15547210/modify-default-value-in-sql-server) — Rowland Shaw, Feb 13 '17 at 10:55

Szymon · Accepted Answer · 2014-03-04T00:22:11.633

20

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]

edited Mar 04 '14 at 00:22

answered Dec 03 '13 at 23:18

Szymon

42,577
16
96
114

So i have to alter the default constraints for each column under different names – Paperwaste Dec 03 '13 at 23:24
1

Yes, that's right. This way you can later refer to those constraints by name. If you don't use your own name, SLQ Server will generate one for you anyway. – Szymon Dec 03 '13 at 23:25

Hart CO · Answer 2 · 2013-12-03T23:21:53.660

2

The syntax is:

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

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

edited Dec 03 '13 at 23:21

answered Dec 03 '13 at 23:15

Hart CO

34,064
6
48
63

score 2 · Answer 3 · answered Nov 20 '20 at 10:03

When you create a constraint without a name, the SQL Server automatically create internal name. It can be see in the [sys].[default_constraints].

For example:

DROP TABLE IF EXISTS [dbo].[StackOverflow];

CREATE TABLE [dbo].[StackOverflow]
(
    [colA] INT
   ,[colB] INT DEFAULT(0)
   ,[colC] CHAR(1) DEFAULT('x')
);

GO

SELECT *
FROM [sys].[default_constraints]
WHERE [parent_object_id] = OBJECT_ID('[dbo].[StackOverflow]');

GO

You can use the name to drop the constraint.

If you have more of them, you can use one of the scripts below:

-- all suported editions
DECLARE @DynamicTSQLStataement NVARCHAR(MAX)
       ,@TableNameWithSchema SYSNAME;

SET @TableNameWithSchema = '[dbo].[StackOverflow]';

SELECT @DynamicTSQLStataement = STUFF
(
    (
        SELECT ';ALTER TABLE ' + @TableNameWithSchema + ' DROP CONSTRAINT ' + QUOTENAME([name])
        FROM [sys].[default_constraints]
        WHERE [parent_object_id] = OBJECT_ID('[dbo].[StackOverflow]')
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)')
    ,1
    ,1
    ,''
);

EXEC sp_executesql @DynamicTSQLStataement;

GO

or

-- SQL Server 2016 or later

DECLARE @DynamicTSQLStataement NVARCHAR(MAX)
       ,@TableNameWithSchema SYSNAME;

SET @TableNameWithSchema = '[dbo].[StackOverflow]';

SELECT @DynamicTSQLStataement = STRING_AGG('ALTER TABLE ' + @TableNameWithSchema + ' DROP CONSTRAINT ' + QUOTENAME([name]), ';')
FROM [sys].[default_constraints]
WHERE [parent_object_id] = OBJECT_ID('[dbo].[StackOverflow]');

EXEC sp_executesql @DynamicTSQLStataement;

GO

That last bit for server 2016 or later is a mighty cool trick. Saved me ages! This should be the accepted answer now but it's probably hard for a beginner to decipher. — AppFzx, Dec 09 '21 at 13:36

altering DEFAULT constraint on column SQL Server

3 Answers3

Linked

Related