none
Adding new table with new constraint broke publishing DB project

    Question

  • Hello,

    I have publishable DB project. Adding the following simple table make it unpublishable on Sol server 2016! However it is still publishable on other versions like 2017, 2015,..

     

        CREATE TABLE [dbo].[Log](
        [Id] [int] IDENTITY(1,1) NOT NULL,
        [Message] [nvarchar](255) NOT NULL,
        [TechnicalMessage] [nvarchar](255) NULL,
        [Level] [nvarchar](255) NOT NULL,
        [Time] [datetime] NOT NULL, 
    CONSTRAINT [PK_Log] PRIMARY KEY CLUSTERED 
    (
        [Id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]

     

     

    It complains that the constraint already existed in DB, but it is not!

    When executing this script on sql server 2016 manually not by publishing it, then it works!

    Note: It complains that the constraint is existed in other table. When delete that constraint it works even with Sql server 2016. Of course this not true because it is not the same constraint name.

    Thanks in advanced,

    Alaa

    Thursday, June 07, 2018 7:16 AM

All replies

  • What do you mean by execution manually? I copied your sample and run on SQL 16 and it worked just fine 

    BTW, there is no version 2015

    Run

    DROP TABLE IF EXISTS [dbo].[Log]
    CREATE TABLE dbo.[Log] (
        [Id] [int] IDENTITY(1,1) NOT NULL,
        [Message] [nvarchar](255) NOT NULL,
        [TechnicalMessage] [nvarchar](255) NULL,
        [Level] [nvarchar](255) NOT NULL,
        [Time] [datetime] NOT NULL, 
    CONSTRAINT [PK_Log] PRIMARY KEY CLUSTERED 
    (
        [Id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, June 07, 2018 10:31 AM
    Moderator
  • Note: It complains that the constraint is existed in other table. When delete that constraint it works even with Sql server 2016. Of course this not true because it is not the same constraint name.

    Are you using an SSDT database project?

    When you say you deleted that constraint, do you mean removed the PK on the new dbo.Log table in the database project or that you dropped a constraint from some other table in the target database that had a different constraint name?

    Run this query in the target SQL 2016 database to see if other objects exist with the same name as the new constraint:

    SELECT OBJECT_SCHEMA_NAME(parent_object_id), OBJECT_NAME(parent_object_id), type
    FROM sys.objects
    WHERE name = N'PK_Log';


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Thursday, June 07, 2018 11:14 AM
    Moderator