0

I am having trouble adding foreign key constraint to my table. I have two schemas in my db 'XYZ' and 'ABC' and I want to reference a table from one schema to another. The query I have mentioned below is working fine on SQL server however when building the solution on VS2015 I am getting SQL71501 error. 

CREATE TABLE [XYZ].[ProductData]
(
    [ID] [int] IDENTITY(100,1) NOT NULL,
    [ExchangePlanID] [varchar](25) NULL,
    [OracleFinanceMarketNbr] [int] NULL,
    [IssueStateCode] [char](2) NULL,
    [PlanID] [varchar](50) NULL,
    [PrimaryPlatformCode] [char](4) NULL,
    [PlanYear] smallint NOT NULL,
    [VersionRefID] int NOT NULL,
    [InsertedBy] [varchar](50) NOT NULL DEFAULT (suser_sname()),
    [InsertedOnUTC] [datetime] NOT NULL DEFAULT (getutcdate()),
    [ModifiedBy] [varchar](50) NOT NULL DEFAULT (suser_sname()),
    [ModifiedOnUTC] [datetime] NOT NULL DEFAULT (getutcdate()),
PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [XYZ].[ProductData]  WITH CHECK ADD  CONSTRAINT [FK_ProductData_VersionRefID] 
FOREIGN KEY([VersionRefId]) REFERENCES [ABC].[VersionInfo]  ([ID])
GO

Table [ABC].[VersionInfo] is alread present in the DB.

If am referncing a table from same schema i.e. 'XYZ' then I am not gettting any error while building my solution.

Could anyone point me in the right direction ?

S7H
  • 1,421
  • 1
  • 22
  • 37
  • Just checking - you refer to ABC.VersionRefInfo but the constraint says ABC.VersionInfo - is one or the other just a typo? – James Casey Nov 22 '16 at 13:20
  • http://stackoverflow.com/questions/4452132/add-foreign-key-relationship-between-two-databases – NefariousB Nov 22 '16 at 13:22
  • @JamesCasey It's a typo here, not in my query. Thanks for pointing out though. – S7H Nov 22 '16 at 13:22
  • @NefariousB this example you have mentioned is for tables present in different DBs. I have my tables in same db but in different schema. – S7H Nov 22 '16 at 13:26
  • If it is working fine in SQL Server than why do you need to do it again in VS ? – GuidoG Nov 22 '16 at 14:13
  • @GuidoG so that I can checkin my changes and migrate them to higher environments. I can only execute my query in dev environment. – S7H Nov 22 '16 at 14:20
  • OK but you dont need VS to do that. You can put changes in script files that you can also check in and run on other databases. That is how I do it I learned the hard way that VS is having troubles in leaving your sql statements untouched. – GuidoG Nov 22 '16 at 14:23
  • @GuidoG We migrate our changes through tfs only and there is a gated checkin policy in our SSDT solution. So unless this error is resolved I can't move my changes to dev. We can create a script and move the changes as well but we only execute these scripts when we directly move changes to Stage or production environments. Since I am still in development stage I can't create script and move my changes till QA. – S7H Nov 22 '16 at 14:29
  • try dropping the constraint on sql server and then adding it in VS? – DForck42 Nov 22 '16 at 16:17
  • Have you run a schema compare to compare the working DB to your project and see where the differences lie? That might be the easiest thing to try right now. – Peter Schott Nov 22 '16 at 22:15
  • Is there an error in the file that defines `[ABC].[VersionInfo] ` ? If so, this will prevent this table from being added to the model, and thus prevent the FK relationship from being validated. – Gavin Campbell Nov 23 '16 at 14:57

1 Answers1

1

You need to have everything included or referenced in the SSDT project. That means you need CREATE SCHEMA [ABC] somewhere, as well as CREATE TABLE [ABC].[VersionInfo] ( ... ). (This assumes you either already have CREATE SCHEMA [XYZ], or [XYZ] is really [dbo], which you usually get for free.)

Andrew Barnett
  • 5,066
  • 1
  • 22
  • 25