The UPDATE statement conflicted with the FOREIGN KEY

Ive got an issue updating a field in an MS CRM (SQL) database

Basically i have a load of accounts with no primary contact, however, there are multiple contacts for each account....

Update dbo.accountbase
Set accountbase.[primarycontactid] = dbo.contactbase.[accountid]
From dbo.accountbase
Inner Join [contactbase]
on dbo.accountbase.[accountid] = dbo.contactbase.[accountid]
where accountbase.[primarycontactid] is null and dbo.contactbase.fullname is not null and dbo.accountbase.deletionstatecode = 0

Gives me....
Msg 547, Level 16, State 0, Line 1
The UPDATE statement conflicted with the FOREIGN KEY constraint "account_primary_contact". The conflict occurred in database "CRM_MSCRM", table "dbo.ContactBase", column 'ContactId'.
The statement has been terminated.

I just want to pick the first contact availible, how can i modify this query to update the account with only the first contact?

Thanks
LVL 2
Dave_Angel_PortsmouthAsked:
Who is Participating?
 
SStoryCommented:
Update accountbase a
INNER JOIN contactbase c
ON a.accountid=c.accountid
set a.primarycontactid = c.accountid
where (a.primarycontactid is null)
   AND (c.fullname is not null)
   AND (a.deletionstatecode = 0)


This is closer, but not right.
I am reading, and it appears that maybe the ROW_NUMBER with PARTITION may do this:
http://msdn.microsoft.com/en-us/library/ms186734.aspx
0
 
SStoryCommented:
I would try to Inner join on a subquery that gets the top 1 contact for each account number
0
 
Dave_Angel_PortsmouthAuthor Commented:
Thanks SStory, how would i do that?
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
SStoryCommented:

OK wait just a minute.

You are setting the contactid = accountid

Is that correct?

Are contactID just account ID's.  It would seem to me that an account would have more than one contact which is what you said and that this wouldn't be correct.  Please let me know.  Also the table structure would help as I don't have whatever product you are using.
0
 
Dave_Angel_PortsmouthAuthor Commented:
Yes thats right.

The contacts have a one to many relationship with accounts, but there can only be one primary contact for the account.

So i am just trying to populate the primary contact with any of the availible contact ids for that account
0
 
Dave_Angel_PortsmouthAuthor Commented:
here is the contactbase table...
USE [NxteraCRM_MSCRM]
GO
/****** Object:  Table [dbo].[ContactBase]    Script Date: 05/01/2009 15:08:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ContactBase](
	[ContactId] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
	[DefaultPriceLevelId] [uniqueidentifier] NULL,
	[CustomerSizeCode] [int] NULL,
	[CustomerTypeCode] [int] NULL,
	[PreferredContactMethodCode] [int] NULL,
	[LeadSourceCode] [int] NULL,
	[DeletionStateCode] [int] NOT NULL,
	[OriginatingLeadId] [uniqueidentifier] NULL,
	[OwningBusinessUnit] [uniqueidentifier] NULL,
	[OwningUser] [uniqueidentifier] NULL,
	[PaymentTermsCode] [int] NULL,
	[ShippingMethodCode] [int] NULL,
	[OwningTeam] [uniqueidentifier] NULL,
	[AccountId] [uniqueidentifier] NULL,
	[ParticipatesInWorkflow] [bit] NULL CONSTRAINT [Set_To_Zero103]  DEFAULT ((0)),
	[IsBackofficeCustomer] [bit] NULL,
	[Salutation] [nvarchar](100) NULL,
	[JobTitle] [nvarchar](100) NULL,
	[FirstName] [nvarchar](50) NULL,
	[Department] [nvarchar](100) NULL,
	[NickName] [nvarchar](50) NULL,
	[MiddleName] [nvarchar](50) NULL,
	[LastName] [nvarchar](50) NULL,
	[Suffix] [nvarchar](10) NULL,
	[YomiFirstName] [nvarchar](150) NULL,
	[FullName] [nvarchar](160) NULL,
	[YomiMiddleName] [nvarchar](150) NULL,
	[YomiLastName] [nvarchar](150) NULL,
	[Anniversary] [datetime] NULL,
	[BirthDate] [datetime] NULL,
	[GovernmentId] [nvarchar](50) NULL,
	[YomiFullName] [nvarchar](450) NULL,
	[Description] [nvarchar](max) NULL,
	[EmployeeId] [nvarchar](50) NULL,
	[GenderCode] [int] NULL,
	[AnnualIncome] [money] NULL,
	[HasChildrenCode] [int] NULL,
	[EducationCode] [int] NULL,
	[WebSiteUrl] [nvarchar](200) NULL,
	[FamilyStatusCode] [int] NULL,
	[FtpSiteUrl] [nvarchar](200) NULL,
	[EMailAddress1] [nvarchar](100) NULL,
	[SpousesName] [nvarchar](100) NULL,
	[AssistantName] [nvarchar](100) NULL,
	[EMailAddress2] [nvarchar](100) NULL,
	[AssistantPhone] [nvarchar](50) NULL,
	[EMailAddress3] [nvarchar](100) NULL,
	[DoNotPhone] [bit] NULL CONSTRAINT [DF_ContactBase_DoNotPhone]  DEFAULT ((0)),
	[ManagerName] [nvarchar](100) NULL,
	[ManagerPhone] [nvarchar](50) NULL,
	[DoNotFax] [bit] NULL CONSTRAINT [DF_ContactBase_DoNotFax]  DEFAULT ((0)),
	[DoNotEMail] [bit] NULL CONSTRAINT [DF_ContactBase_DoNotEMail]  DEFAULT ((0)),
	[DoNotPostalMail] [bit] NULL CONSTRAINT [DF_ContactBase_DoNotPostalMail]  DEFAULT ((0)),
	[DoNotBulkEMail] [bit] NULL CONSTRAINT [DF_ContactBase_DoNotBulkEMail]  DEFAULT ((0)),
	[DoNotBulkPostalMail] [bit] NULL CONSTRAINT [DF_ContactBase_DoNotBulkPostalMail]  DEFAULT ((0)),
	[AccountRoleCode] [int] NULL,
	[TerritoryCode] [int] NULL,
	[IsPrivate] [bit] NULL CONSTRAINT [Set_To_Zero104]  DEFAULT ((0)),
	[CreditLimit] [money] NULL,
	[CreatedOn] [datetime] NULL,
	[CreditOnHold] [bit] NULL,
	[CreatedBy] [uniqueidentifier] NULL,
	[ModifiedOn] [datetime] NULL,
	[ModifiedBy] [uniqueidentifier] NULL,
	[NumberOfChildren] [int] NULL,
	[ChildrensNames] [nvarchar](255) NULL,
	[VersionNumber] [timestamp] NULL,
	[MobilePhone] [nvarchar](50) NULL,
	[Pager] [nvarchar](50) NULL,
	[Telephone1] [nvarchar](50) NULL,
	[Telephone2] [nvarchar](50) NULL,
	[Telephone3] [nvarchar](50) NULL,
	[Fax] [nvarchar](50) NULL,
	[Aging30] [money] NULL,
	[StateCode] [int] NOT NULL,
	[Aging60] [money] NULL,
	[StatusCode] [int] NULL,
	[Aging90] [money] NULL,
	[ParentContactId] [uniqueidentifier] NULL,
	[PreferredSystemUserId] [uniqueidentifier] NULL,
	[PreferredServiceId] [uniqueidentifier] NULL,
	[MasterId] [uniqueidentifier] NULL,
	[PreferredAppointmentDayCode] [int] NULL,
	[PreferredAppointmentTimeCode] [int] NULL,
	[DoNotSendMM] [bit] NULL CONSTRAINT [DF_ContactBase_DoNotSendMM]  DEFAULT ((0)),
	[Merged] [bit] NULL CONSTRAINT [DF_ContactBase_Merged]  DEFAULT ((0)),
	[ExternalUserIdentifier] [nvarchar](50) NULL,
	[SubscriptionId] [uniqueidentifier] NULL,
	[PreferredEquipmentId] [uniqueidentifier] NULL,
	[LastUsedInCampaign] [datetime] NULL,
	[TransactionCurrencyId] [uniqueidentifier] NULL,
	[OverriddenCreatedOn] [datetime] NULL,
	[ExchangeRate] [decimal](23, 10) NULL,
	[ImportSequenceNumber] [int] NULL,
	[TimeZoneRuleVersionNumber] [int] NULL,
	[UTCConversionTimeZoneCode] [int] NULL,
	[AnnualIncome_Base] [money] NULL,
	[CreditLimit_Base] [money] NULL,
	[Aging60_Base] [money] NULL,
	[Aging90_Base] [money] NULL,
	[Aging30_Base] [money] NULL,
 CONSTRAINT [cndx_PrimaryKey_Contact] PRIMARY KEY CLUSTERED 
(
	[ContactId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
 
GO
ALTER TABLE [dbo].[ContactBase]  WITH NOCHECK ADD  CONSTRAINT [account_contacts] FOREIGN KEY([AccountId])
REFERENCES [dbo].[AccountBase] ([AccountId])
NOT FOR REPLICATION 
GO
ALTER TABLE [dbo].[ContactBase] CHECK CONSTRAINT [account_contacts]
GO
ALTER TABLE [dbo].[ContactBase]  WITH NOCHECK ADD  CONSTRAINT [business_unit_contacts] FOREIGN KEY([OwningBusinessUnit])
REFERENCES [dbo].[BusinessUnitBase] ([BusinessUnitId])
NOT FOR REPLICATION 
GO
ALTER TABLE [dbo].[ContactBase] CHECK CONSTRAINT [business_unit_contacts]
GO
ALTER TABLE [dbo].[ContactBase]  WITH NOCHECK ADD  CONSTRAINT [contact_master_contact] FOREIGN KEY([MasterId])
REFERENCES [dbo].[ContactBase] ([ContactId])
NOT FOR REPLICATION 
GO
ALTER TABLE [dbo].[ContactBase] CHECK CONSTRAINT [contact_master_contact]
GO
ALTER TABLE [dbo].[ContactBase]  WITH NOCHECK ADD  CONSTRAINT [contact_originating_lead] FOREIGN KEY([OriginatingLeadId])
REFERENCES [dbo].[LeadBase] ([LeadId])
NOT FOR REPLICATION 
GO
ALTER TABLE [dbo].[ContactBase] CHECK CONSTRAINT [contact_originating_lead]
GO
ALTER TABLE [dbo].[ContactBase]  WITH NOCHECK ADD  CONSTRAINT [contact_owning_user] FOREIGN KEY([OwningUser])
REFERENCES [dbo].[SystemUserBase] ([SystemUserId])
NOT FOR REPLICATION 
GO
ALTER TABLE [dbo].[ContactBase] CHECK CONSTRAINT [contact_owning_user]
GO
ALTER TABLE [dbo].[ContactBase]  WITH NOCHECK ADD  CONSTRAINT [contact_parent_contact] FOREIGN KEY([ParentContactId])
REFERENCES [dbo].[ContactBase] ([ContactId])
NOT FOR REPLICATION 
GO
ALTER TABLE [dbo].[ContactBase] CHECK CONSTRAINT [contact_parent_contact]
GO
ALTER TABLE [dbo].[ContactBase]  WITH NOCHECK ADD  CONSTRAINT [equipment_contacts] FOREIGN KEY([PreferredEquipmentId])
REFERENCES [dbo].[EquipmentBase] ([EquipmentId])
NOT FOR REPLICATION 
GO
ALTER TABLE [dbo].[ContactBase] CHECK CONSTRAINT [equipment_contacts]
GO
ALTER TABLE [dbo].[ContactBase]  WITH NOCHECK ADD  CONSTRAINT [price_level_contacts] FOREIGN KEY([DefaultPriceLevelId])
REFERENCES [dbo].[PriceLevelBase] ([PriceLevelId])
NOT FOR REPLICATION 
GO
ALTER TABLE [dbo].[ContactBase] CHECK CONSTRAINT [price_level_contacts]
GO
ALTER TABLE [dbo].[ContactBase]  WITH NOCHECK ADD  CONSTRAINT [service_contacts] FOREIGN KEY([PreferredServiceId])
REFERENCES [dbo].[ServiceBase] ([ServiceId])
NOT FOR REPLICATION 
GO
ALTER TABLE [dbo].[ContactBase] CHECK CONSTRAINT [service_contacts]
GO
ALTER TABLE [dbo].[ContactBase]  WITH NOCHECK ADD  CONSTRAINT [system_user_contacts] FOREIGN KEY([PreferredSystemUserId])
REFERENCES [dbo].[SystemUserBase] ([SystemUserId])
NOT FOR REPLICATION 
GO
ALTER TABLE [dbo].[ContactBase] CHECK CONSTRAINT [system_user_contacts]
GO
ALTER TABLE [dbo].[ContactBase]  WITH NOCHECK ADD  CONSTRAINT [team_contacts] FOREIGN KEY([OwningTeam])
REFERENCES [dbo].[TeamBase] ([TeamId])
NOT FOR REPLICATION 
GO
ALTER TABLE [dbo].[ContactBase] CHECK CONSTRAINT [team_contacts]
GO
ALTER TABLE [dbo].[ContactBase]  WITH NOCHECK ADD  CONSTRAINT [transactioncurrency_contact] FOREIGN KEY([TransactionCurrencyId])
REFERENCES [dbo].[TransactionCurrencyBase] ([TransactionCurrencyId])
NOT FOR REPLICATION 
GO
ALTER TABLE [dbo].[ContactBase] CHECK CONSTRAINT [transactioncurrency_contact]

Open in new window

0
 
Dave_Angel_PortsmouthAuthor Commented:
Here is the accountbase table...
USE [NxteraCRM_MSCRM]
GO
/****** Object:  Table [dbo].[AccountBase]    Script Date: 05/01/2009 15:09:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[AccountBase](
	[AccountId] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
	[AccountCategoryCode] [int] NULL,
	[TerritoryId] [uniqueidentifier] NULL,
	[DefaultPriceLevelId] [uniqueidentifier] NULL,
	[CustomerSizeCode] [int] NULL,
	[PreferredContactMethodCode] [int] NULL,
	[CustomerTypeCode] [int] NULL,
	[AccountRatingCode] [int] NULL,
	[IndustryCode] [int] NULL,
	[TerritoryCode] [int] NULL,
	[AccountClassificationCode] [int] NULL,
	[DeletionStateCode] [int] NOT NULL,
	[BusinessTypeCode] [int] NULL,
	[OwningBusinessUnit] [uniqueidentifier] NULL,
	[OwningTeam] [uniqueidentifier] NULL,
	[OwningUser] [uniqueidentifier] NULL,
	[OriginatingLeadId] [uniqueidentifier] NULL,
	[PaymentTermsCode] [int] NULL,
	[ShippingMethodCode] [int] NULL,
	[PrimaryContactId] [uniqueidentifier] NULL,
	[ParticipatesInWorkflow] [bit] NULL CONSTRAINT [Set_To_Zero93]  DEFAULT ((0)),
	[Name] [nvarchar](160) NULL,
	[AccountNumber] [nvarchar](20) NULL,
	[Revenue] [money] NULL,
	[NumberOfEmployees] [int] NULL,
	[Description] [nvarchar](max) NULL,
	[SIC] [nvarchar](20) NULL,
	[OwnershipCode] [int] NULL,
	[MarketCap] [money] NULL,
	[SharesOutstanding] [int] NULL,
	[TickerSymbol] [nvarchar](10) NULL,
	[StockExchange] [nvarchar](20) NULL,
	[WebSiteURL] [nvarchar](200) NULL,
	[FtpSiteURL] [nvarchar](200) NULL,
	[EMailAddress1] [nvarchar](100) NULL,
	[EMailAddress2] [nvarchar](100) NULL,
	[EMailAddress3] [nvarchar](100) NULL,
	[DoNotPhone] [bit] NULL CONSTRAINT [DF_AccountBase_DoNotPhone]  DEFAULT ((0)),
	[DoNotFax] [bit] NULL CONSTRAINT [DF_AccountBase_DoNotFax]  DEFAULT ((0)),
	[Telephone1] [nvarchar](50) NULL,
	[DoNotEMail] [bit] NULL CONSTRAINT [DF_AccountBase_DoNotEMail]  DEFAULT ((0)),
	[Telephone2] [nvarchar](50) NULL,
	[Fax] [nvarchar](50) NULL,
	[Telephone3] [nvarchar](50) NULL,
	[DoNotPostalMail] [bit] NULL CONSTRAINT [DF_AccountBase_DoNotPostalMail]  DEFAULT ((0)),
	[DoNotBulkEMail] [bit] NULL CONSTRAINT [DF_AccountBase_DoNotBulkEMail]  DEFAULT ((0)),
	[DoNotBulkPostalMail] [bit] NULL CONSTRAINT [DF_AccountBase_DoNotBulkPostalMail]  DEFAULT ((0)),
	[CreditLimit] [money] NULL,
	[CreditOnHold] [bit] NULL,
	[IsPrivate] [bit] NULL CONSTRAINT [Set_To_Zero94]  DEFAULT ((0)),
	[CreatedOn] [datetime] NULL,
	[CreatedBy] [uniqueidentifier] NULL,
	[ModifiedOn] [datetime] NULL,
	[ModifiedBy] [uniqueidentifier] NULL,
	[VersionNumber] [timestamp] NULL,
	[ParentAccountId] [uniqueidentifier] NULL,
	[Aging30] [money] NULL,
	[StateCode] [int] NOT NULL,
	[Aging60] [money] NULL,
	[StatusCode] [int] NULL,
	[Aging90] [money] NULL,
	[PreferredAppointmentDayCode] [int] NULL,
	[PreferredSystemUserId] [uniqueidentifier] NULL,
	[PreferredAppointmentTimeCode] [int] NULL,
	[Merged] [bit] NULL CONSTRAINT [DF_AccountBase_Merged]  DEFAULT ((0)),
	[DoNotSendMM] [bit] NULL CONSTRAINT [DF_AccountBase_DoNotSendMM]  DEFAULT ((0)),
	[MasterId] [uniqueidentifier] NULL,
	[LastUsedInCampaign] [datetime] NULL,
	[PreferredServiceId] [uniqueidentifier] NULL,
	[PreferredEquipmentId] [uniqueidentifier] NULL,
	[ExchangeRate] [decimal](23, 10) NULL,
	[UTCConversionTimeZoneCode] [int] NULL,
	[OverriddenCreatedOn] [datetime] NULL,
	[TimeZoneRuleVersionNumber] [int] NULL,
	[ImportSequenceNumber] [int] NULL,
	[TransactionCurrencyId] [uniqueidentifier] NULL,
	[CreditLimit_Base] [money] NULL,
	[Aging30_Base] [money] NULL,
	[Revenue_Base] [money] NULL,
	[Aging90_Base] [money] NULL,
	[MarketCap_Base] [money] NULL,
	[Aging60_Base] [money] NULL,
	[YomiName] [nvarchar](160) NULL,
 CONSTRAINT [cndx_PrimaryKey_Account] PRIMARY KEY CLUSTERED 
(
	[AccountId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
 
GO
ALTER TABLE [dbo].[AccountBase]  WITH NOCHECK ADD  CONSTRAINT [account_master_account] FOREIGN KEY([MasterId])
REFERENCES [dbo].[AccountBase] ([AccountId])
NOT FOR REPLICATION 
GO
ALTER TABLE [dbo].[AccountBase] CHECK CONSTRAINT [account_master_account]
GO
ALTER TABLE [dbo].[AccountBase]  WITH NOCHECK ADD  CONSTRAINT [account_originating_lead] FOREIGN KEY([OriginatingLeadId])
REFERENCES [dbo].[LeadBase] ([LeadId])
NOT FOR REPLICATION 
GO
ALTER TABLE [dbo].[AccountBase] CHECK CONSTRAINT [account_originating_lead]
GO
ALTER TABLE [dbo].[AccountBase]  WITH NOCHECK ADD  CONSTRAINT [account_parent_account] FOREIGN KEY([ParentAccountId])
REFERENCES [dbo].[AccountBase] ([AccountId])
NOT FOR REPLICATION 
GO
ALTER TABLE [dbo].[AccountBase] CHECK CONSTRAINT [account_parent_account]
GO
ALTER TABLE [dbo].[AccountBase]  WITH NOCHECK ADD  CONSTRAINT [account_primary_contact] FOREIGN KEY([PrimaryContactId])
REFERENCES [dbo].[ContactBase] ([ContactId])
NOT FOR REPLICATION 
GO
ALTER TABLE [dbo].[AccountBase] CHECK CONSTRAINT [account_primary_contact]
GO
ALTER TABLE [dbo].[AccountBase]  WITH NOCHECK ADD  CONSTRAINT [business_unit_accounts] FOREIGN KEY([OwningBusinessUnit])
REFERENCES [dbo].[BusinessUnitBase] ([BusinessUnitId])
NOT FOR REPLICATION 
GO
ALTER TABLE [dbo].[AccountBase] CHECK CONSTRAINT [business_unit_accounts]
GO
ALTER TABLE [dbo].[AccountBase]  WITH NOCHECK ADD  CONSTRAINT [equipment_accounts] FOREIGN KEY([PreferredEquipmentId])
REFERENCES [dbo].[EquipmentBase] ([EquipmentId])
NOT FOR REPLICATION 
GO
ALTER TABLE [dbo].[AccountBase] CHECK CONSTRAINT [equipment_accounts]
GO
ALTER TABLE [dbo].[AccountBase]  WITH NOCHECK ADD  CONSTRAINT [price_level_accounts] FOREIGN KEY([DefaultPriceLevelId])
REFERENCES [dbo].[PriceLevelBase] ([PriceLevelId])
NOT FOR REPLICATION 
GO
ALTER TABLE [dbo].[AccountBase] CHECK CONSTRAINT [price_level_accounts]
GO
ALTER TABLE [dbo].[AccountBase]  WITH NOCHECK ADD  CONSTRAINT [service_accounts] FOREIGN KEY([PreferredServiceId])
REFERENCES [dbo].[ServiceBase] ([ServiceId])
NOT FOR REPLICATION 
GO
ALTER TABLE [dbo].[AccountBase] CHECK CONSTRAINT [service_accounts]
GO
ALTER TABLE [dbo].[AccountBase]  WITH NOCHECK ADD  CONSTRAINT [system_user_accounts] FOREIGN KEY([PreferredSystemUserId])
REFERENCES [dbo].[SystemUserBase] ([SystemUserId])
NOT FOR REPLICATION 
GO
ALTER TABLE [dbo].[AccountBase] CHECK CONSTRAINT [system_user_accounts]
GO
ALTER TABLE [dbo].[AccountBase]  WITH NOCHECK ADD  CONSTRAINT [team_accounts] FOREIGN KEY([OwningTeam])
REFERENCES [dbo].[TeamBase] ([TeamId])
NOT FOR REPLICATION 
GO
ALTER TABLE [dbo].[AccountBase] CHECK CONSTRAINT [team_accounts]
GO
ALTER TABLE [dbo].[AccountBase]  WITH NOCHECK ADD  CONSTRAINT [territory_accounts] FOREIGN KEY([TerritoryId])
REFERENCES [dbo].[TerritoryBase] ([TerritoryId])
NOT FOR REPLICATION 
GO
ALTER TABLE [dbo].[AccountBase] CHECK CONSTRAINT [territory_accounts]
GO
ALTER TABLE [dbo].[AccountBase]  WITH NOCHECK ADD  CONSTRAINT [transactioncurrency_account] FOREIGN KEY([TransactionCurrencyId])
REFERENCES [dbo].[TransactionCurrencyBase] ([TransactionCurrencyId])
NOT FOR REPLICATION 
GO
ALTER TABLE [dbo].[AccountBase] CHECK CONSTRAINT [transactioncurrency_account]
GO
ALTER TABLE [dbo].[AccountBase]  WITH NOCHECK ADD  CONSTRAINT [user_accounts] FOREIGN KEY([OwningUser])
REFERENCES [dbo].[SystemUserBase] ([SystemUserId])
NOT FOR REPLICATION 
GO
ALTER TABLE [dbo].[AccountBase] CHECK CONSTRAINT [user_accounts]

Open in new window

0
 
SStoryCommented:
Well, what I am saying is assigning an accountid to a contactid seemed wrong.
0
 
SStoryCommented:
According to the defs you have presented it is wrong.

Accountbase and Contactbase both have an AccountID field

the PK of Accountbase appears to be AccountID
the PK of contactBase is probably ContactID

The FK from contactBase to the PK of AccountBase would be the ContactBase.AccountID

You had:
Set accountbase.[primarycontactid] = dbo.contactbase.[accountid]

This is incorrect -- apples to oranges

This would be apples to apples:
Set accountbase.[primarycontactid] = dbo.contactbase.[contactid]
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.