Red Gate forums :: View topic - Unnamed default constraints become named constraints
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Comparison SDK Previous Versions
SQL Comparison SDK Previous Versions forum

Unnamed default constraints become named constraints

Search in SQL Comparison SDK Previous Versions forum
Post new topic   Reply to topic
Jump to:  
Author Message
chrisdar



Joined: 15 Sep 2009
Posts: 2

PostPosted: Tue Sep 22, 2009 4:47 pm    Post subject: Unnamed default constraints become named constraints Reply with quote

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...
Back to top
View user's profile Send private message
Chris Auckland



Joined: 24 Oct 2006
Posts: 757
Location: Red Gate Software Ltd.

PostPosted: Thu Sep 24, 2009 9:17 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
chrisdar



Joined: 15 Sep 2009
Posts: 2

PostPosted: Thu Sep 24, 2009 4:27 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
Chris Auckland



Joined: 24 Oct 2006
Posts: 757
Location: Red Gate Software Ltd.

PostPosted: Mon Sep 28, 2009 11:00 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group