Unnamed default constraints become named constraints

Automate and integrate using the SQL comparison API

Moderator: Chris Auckland

Unnamed default constraints become named constraints

Postby chrisdar » Tue Sep 22, 2009 4:47 pm

I am evaluating the SQL Comparison SDK as a tool to automate in-place upgrade of databases. I have hit a snag that relates to default constraints.

I have a v. N-1 table with a date column. Using SMO to generate a create script yields:
CREATE TABLE [dbo].[PipelineLog](
[logOrder] [int] IDENTITY(1,1) NOT NULL,
[component] [nvarchar](255) NULL,
[date] [datetime] NULL,
[text] [nvarchar](max) NULL,
CONSTRAINT [PK_PipelineLog] PRIMARY KEY CLUSTERED
(
[logOrder] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


In v. N the column has a default, unnamed constraint applied, which I want applied during the upgrade. In the model database, the create script for the table is:
CREATE TABLE [dbo].[PipelineLog](
[logOrder] [int] IDENTITY(1,1) NOT NULL,
[component] [nvarchar](255) NULL,
[date] [datetime] NULL,
[text] [nvarchar](max) NULL,
CONSTRAINT [PK_PipelineLog] PRIMARY KEY CLUSTERED
(
[logOrder] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[PipelineLog] ADD DEFAULT (getutcdate()) FOR [date]
GO

After upgrade, the default constraint is correctly applied, but it is named:
CREATE TABLE [dbo].[PipelineLog](
[logOrder] [int] IDENTITY(1,1) NOT NULL,
[component] [nvarchar](255) NULL,
[date] [datetime] NULL,
[text] [nvarchar](max) NULL,
CONSTRAINT [PK_PipelineLog] PRIMARY KEY CLUSTERED
(
[logOrder] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[PipelineLog] ADD CONSTRAINT [DF__PipelineLo__date__0519C6AF] DEFAULT (getutcdate()) FOR [date]
GO

Is there an option to control this behavior so that the default constraint will be unnamed after upgrade?

I am using an SMO-based approach to verify the upgrade against the model as a post-upgrade verification step. Thus far, this is the only difference I've found that I haven't been able to work around. Note that I prefer to have verification independent of the RedGate SDK since it is the SDK's actions that the verification pass is auditing...
chrisdar
 
Posts: 2
Joined: Tue Sep 15, 2009 11:23 pm

Postby Chris Auckland » Thu Sep 24, 2009 9:17 am

Thanks for your post.

I don't think you can stop SQL Server automatically generating the constraint names when you create the object, but you can ask SQL Compare to ignore them during the comparison.

You can do this using Options.IgnoreConstraintNames

I hope this is heplful.
Chris
Chris Auckland
 
Posts: 757
Joined: Tue Oct 24, 2006 2:12 pm
Location: Red Gate Software Ltd.

Postby chrisdar » Thu Sep 24, 2009 4:27 pm

Thanks for your reply Chris.

The constraint that is applied in the model database somehow is scripted out with an unnamed default. So it is certainly legal in SQL (perhaps Microsoft's flavor of SQL?) to define the default in this way.

Consequently, it seems that the SQL Compare product doesn't support upgrade that maintains this construct. Can you confirm this for me with the product team?

I agree that SQL Compare concludes that these two definitions of the default constraint are equivalent. But I need to validate the upgrade independent of this product, which is why I am using SMO. While the definitions are equivalent, they aren't the same; and that's the goal of in-place database upgrade: to make the database schema the same.

Do you have a whitepaper that documents any other differences that one should expect between the model and upgraded database after the upgrade is performed?

Thanks,
Chris
chrisdar
 
Posts: 2
Joined: Tue Sep 15, 2009 11:23 pm

Postby Chris Auckland » Mon Sep 28, 2009 11:00 am

Thanks for your reply.

When you create the default without defining a name, SQL Server will automatically generate the name. You can see this if you query sys.default_constraints.

When you script the object using SSMS, I believe it checks the 'is_system_named' column, and if it has a value of 1, it doesn't script the constraint name.

When a constraint is generated without an explicit name, it is very unlikely that the exact same name will be automatically generated when you create the same constraint on another database. There will almost certainly be a difference, and this is why SQL Compare gives you the ability to ignore it.

I take your point that as you have created your constraint without explicitly defining a name, SQL Compare should also be able to script the object without defining the name. I have logged a feature request in our system for this: SC-4549.

I hope this helps.
Chris
Chris Auckland
 
Posts: 757
Joined: Tue Oct 24, 2006 2:12 pm
Location: Red Gate Software Ltd.


Return to SQL Comparison SDK Previous Versions

Who is online

Users browsing this forum: No registered users and 1 guest