NOT NULL and default values - error

dmpriso

Hi!

I am using Red Gate SQL Bundle and the .NET components.

I'm however having a problem while updating a database, after I have modified a new column to a table which already contains data.

The column "ProvTeilungstag" has been modified not to accept null values, and I assigned a default value to it. (This worked in Enterprise manager or Query Analyzer, although the table already contains data).


Here's the new table

if exists (select * from dbo.sysobjects where id = object_id(N'[Institute]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [Institute]
GO

CREATE TABLE [Institute] (
	[Institut] [int] IDENTITY (1, 1) NOT NULL ,
	[Institutsname] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
	[Land] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
	[BLZ] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
	[Memo] [varchar] (8000) COLLATE Latin1_General_CI_AS NULL ,
	[Strasse] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
	[Ort] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
	[Telefonnummer] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
	[Faxnummer] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
	[Email] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
	[Homepage] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
	[KAG] [bit] NOT NULL ,
	[Kurzzeichen] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
	[ProvVerzAbschlT] [smallint] NULL ,
	[ProvVerzAbschlM] [smallint] NULL ,
	[ProvVerzBestandT] [smallint] NULL ,
	[ProvVerzBestandM] [smallint] NULL ,
	[ProvVerzFolgeT] [smallint] NULL ,
	[ProvVerzFolgeM] [smallint] NULL ,
	[PartnerInst] [bit] NOT NULL ,
	[Rolloverstart] [smalldatetime] NULL ,
	[ProvVerzIndexT] [int] NULL ,
	[ProvVerzIndexM] [int] NULL ,
	[Briefanrede] [varchar] (70) COLLATE Latin1_General_CI_AS NULL ,
	[LKZ] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
	[ProvTeilungstag] [int] NOT NULL CONSTRAINT [DF_Institute_ProvTeilungstag] DEFAULT (0),
	[MaklerNr] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL CONSTRAINT [DF_Institute_MaklerNr] DEFAULT (''),
	[Detail] [bit] NULL CONSTRAINT [DF_Institute_Detail] DEFAULT (0),
	[BestandStichtag] [datetime] NULL ,
	[BestandInterval] [int] NULL ,
	CONSTRAINT [PK_Institute_1] PRIMARY KEY  CLUSTERED 
	(
		[Institut]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
) ON [PRIMARY]
GO

(the ProvTeilungstag column has been added)

Here's the old table:

if exists (select * from dbo.sysobjects where id = object_id(N'[Institute]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [Institute]
GO

CREATE TABLE [Institute] (
	[Institut] [int] IDENTITY (1, 1) NOT NULL ,
	[Institutsname] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
	[Land] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
	[BLZ] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
	[Memo] [varchar] (8000) COLLATE Latin1_General_CI_AS NULL ,
	[Strasse] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
	[Ort] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
	[Telefonnummer] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
	[Faxnummer] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
	[Email] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
	[Homepage] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
	[KAG] [bit] NOT NULL ,
	[Kurzzeichen] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
	[ProvVerzAbschlT] [smallint] NULL ,
	[ProvVerzAbschlM] [smallint] NULL ,
	[ProvVerzBestandT] [smallint] NULL ,
	[ProvVerzBestandM] [smallint] NULL ,
	[ProvVerzFolgeT] [smallint] NULL ,
	[ProvVerzFolgeM] [smallint] NULL ,
	[PartnerInst] [bit] NOT NULL ,
	[Rolloverstart] [smalldatetime] NULL ,
	[ProvVerzIndexT] [int] NULL ,
	[ProvVerzIndexM] [int] NULL ,
	[Briefanrede] [varchar] (70) COLLATE Latin1_General_CI_AS NULL ,
	[LKZ] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
	[ProvTeilungstag] [int] NULL ,
	[MaklerNr] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL CONSTRAINT [DF_Institute_MaklerNr] DEFAULT (''),
	[Detail] [bit] NULL CONSTRAINT [DF_Institute_Detail] DEFAULT (0),
	[BestandStichtag] [datetime] NULL ,
	[BestandInterval] [int] NULL ,
	CONSTRAINT [PK_Institute_1] PRIMARY KEY  CLUSTERED 
	(
		[Institut]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] ,
	CONSTRAINT [FK_Institute_LKZ] FOREIGN KEY 
	(
		[LKZ]
	) REFERENCES [LKZ] (
		[Kurz]
	) ON UPDATE CASCADE 
) ON [PRIMARY]
GO

Here's the generated script which fails:

...
GO
ALTER TABLE [dbo].[Institute] ALTER COLUMN [ProvTeilungstag] [int] NOT NULL 
GO
...

SQL Compare seems to have forgotten to add the DEFAULT keyword. Are there any fixes / workarounds for this?

Regards

chris sampson

Hi,

SQL Compare shouldn't have forgotten to add the default, it should add it as a constraint further down the script.
The problem here is that changing a column that has NULL data in it to a NOT NULL column will fail, even if a default is associated with it. This is because SQL Server will not convert the existing NULLs to your default automatically.
The only thing that can be done is to write a script to update the NULL values to your specified default value, and run it against the destination database before the SQL Compare script is run. For Example:

UPDATE [dbo].[Institute] SET [ProvTeilungstag] = 0 WHERE [ProvTeilungstag] = NULL