none
Invalid 1750 and 1767 errors after upgrading to SSDT for SQL Server 14 RRS feed

  • Question

  • After upgrading to SSDT for SQL Server 2014, my database won't build. I'm getting a whole slough of 1750 and 1767 errors, e.g:

    Error 20 SQL01767: Foreign key 'FK_CustomerUser_ToCustomer' references invalid table 'Customer'. C:\source\payboard\PayboardWeb\Payboard.Database\CustomerUser.sql 1 1 Payboard.Database
    Error 21 SQL01750: Could not create constraint or index. See previous errors. C:\source\payboard\PayboardWeb\Payboard.Database\CustomerUser.sql 1 1 Payboard.Database
    Error 22 SQL01767: Foreign key 'FK_Customer_ToCustomerUser' references invalid table 'CustomerUser'. C:\source\payboard\PayboardWeb\Payboard.Database\Customer.sql 1 1 Payboard.Database
    Error 23 SQL01750: Could not create constraint or index. See previous errors. C:\source\payboard\PayboardWeb\Payboard.Database\Customer.sql 1 1 Payboard.Database

    The tables are correctly defined - I have no idea why the errors suddenly started showing up. Any suggestions?

    CREATE TABLE [dbo].[CustomerUser]
    
    (
        [CustomerUserId] INT NOT NULL IDENTITY PRIMARY KEY,
        CustomerId int not null,
        ExternalUserId nvarchar(100) null,
        FirstName nvarchar(50) null,
    	LastName nvarchar(50) null,
        Email nvarchar(50) null,
        LastCampaignStepOn datetime2 null,
        UnsubscribedOn datetime2 null,
        IsChampion bit not null default(0),
    	ManagedByUserId int,
        CreatedOn datetime2 not null default getutcdate(),
        ModifiedOn datetime2 not null default getutcdate(), 
        DeletedOn datetime2 null,
        CONSTRAINT [FK_CustomerUser_ToCustomer] FOREIGN KEY (CustomerId) REFERENCES [Customer]([CustomerId]), 
        CONSTRAINT [FK_CustomerUser_ToUser] FOREIGN KEY (ManagedByUserId) REFERENCES [User]([UserId]), 
        CONSTRAINT [CK_CustomerUser_ManagedByUserId] CHECK (dbo.CustomerUserOrganizationMismatches() = 0)
    )
    GO
    
    CREATE INDEX [IX_CustomerUser_CustomerId] ON [dbo].[CustomerUser] ([CustomerId])
    GO
    
    CREATE INDEX [IX_CustomerUser_ExternalCustomerId] ON [dbo].[CustomerUser] ([ExternalUserId])
    GO
    
    CREATE INDEX IX_CustomerUser_Email on dbo.CustomerUser (Email)
    GO
    
    create index ix_CustomerUser_DeletedOn on CustomerUser(DeletedOn)
    go
    
    CREATE INDEX [IX_CustomerUser_ManagedByUserId] ON [dbo].[CustomerUser] (ManagedByUserId)


    And also:

    CREATE TABLE [dbo].[Customer]
    (
        [CustomerId] INT NOT NULL Identity PRIMARY KEY,
        [ExternalCustomerId] nvarchar(100) null,
        [OrganizationId] int not null, 
        [Name] NVARCHAR(50) NULL, 
        [Email] nvarchar(50) null,
        Address1 nvarchar(50) null,
        Address2 nvarchar(50) null,
        City nvarchar(50) null,
        Region nvarchar(50) null,
        PostalCode nvarchar(50) null,
        CountryCode nchar(2) null,
        PrimaryCustomerUserId int null,
    	ManagedByUserId int null,
        [CreatedOn] datetime2 NOT NULL default getutcdate(), 
        [ModifiedOn] datetime2 NOT NULL default getutcdate(),
        [DeletedOn] DateTime2 null,
        CONSTRAINT [FK_Customer_ToOrganization] FOREIGN KEY (OrganizationId) REFERENCES Organization(OrganizationId), 
        CONSTRAINT [FK_Customer_ToCustomerUser] FOREIGN KEY (PrimaryCustomerUserId) REFERENCES [CustomerUser]([CustomerUserId]), 
        CONSTRAINT [FK_Customer_ToUser] FOREIGN KEY (ManagedByUserId) REFERENCES [User]([UserId]), 
        CONSTRAINT [CK_Customer_ManagedByUserId] CHECK (dbo.CustomerOrganizationMismatches() = 0)
    )
    GO
    
    CREATE INDEX [IX_Customer_AltCustomerId] ON [dbo].[Customer] (OrganizationId, [ExternalCustomerId])
    go
    
    create index ix_Customer_DeletedOn on Customer(DeletedOn)
    
    GO
    
    CREATE INDEX [IX_Customer_ManagedByUserId] ON [dbo].[Customer] (ManagedByUserId)
    go
    I'm guessing that the error has something to do with the circular foreign keys, but this wasn't a problem with prior versions of the tools.



    • Edited by smithkl42 Friday, April 11, 2014 5:16 PM
    Friday, April 11, 2014 5:12 PM

All replies

  • Well, I fixed it by breaking out the circular foreign key to a separate ALTER TABLE statement in a different file. But still, odd that this broke when it worked in previous versions of the tools.
    Friday, April 11, 2014 5:23 PM
  • I tried to create a repro and wasn't able to see the issue.  If you have a simple project which illustrates the problem I'd like to investigate.  thanks!  Patrick

    Patrick dot sirr at Microsoft dot com

    Friday, April 11, 2014 9:16 PM
  • Patrick, this error occurs when you change the project target platform which automatically turns on 'Enable extended transact-SQL verification for common objects'. I have many projects that have nullable circular references (sadly I spent many hours rewriting scripts too) , publishing (with flag off) builds a script with ALTER TABLE statements for the foreign keys, obviously validation doesn't work the same way.

    If you're in the mood for fixing that can you look at being able to validate scripts that use INFORMATION_SCHEMA too :-)

    Saturday, May 10, 2014 4:38 PM
  • Unfortunately it's the second time I've update DataTools and got serious problems.

    Now I have 94 tables to be corrected and my solution is not working.


    Fernando Angelieri

    Friday, May 16, 2014 6:20 PM
  • Similar issue introduced with 2014 ssdt (easy/always reproducible): 

    Incorrect SQL 71005 warnings for table variables in latest ssdt



    Thursday, May 22, 2014 6:31 AM