Posted by pbd22 on 07/31/07 21:12

Hello.

I am getting the following error:

"""There are no primary or candidate keys in the referenced table
'UserPrecedence' that match the referencing column list in the foreign
key 'FK_USERS__UserPrecedence'."""

When running the following script:

alter table Users
add constraint FK_USERS__UserPrecedence
foreign key (userID)
references UserPrecedence(owner_userID)

I am just learning my way around Foreign Key assignments involving
surrogate keys. Do I need to be populating the owner_userID column in
UserPrecedence as I populate the userID column in Users? They are
supposed to be the same. I assumed that the PK/FK relationship causes
owner_userID to mimic userID in an automated way without much
intervention needed on my part. I guess I need some advice as to why I
am getting this error.

UserPrecedence Looks like this:

CREATE TABLE [dbo].[UserPrecedence](
[up_order] [int] NOT NULL,
[profile_userID] [bigint] NULL,
[owner_userID] [bigint] NOT NULL,
[search_name] [varchar](50) NULL,
CONSTRAINT [PK_UserPrecedence] PRIMARY KEY CLUSTERED
(
[owner_userID] ASC,
[up_order] 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

And Users Looks Like This:

CREATE TABLE [dbo].[Users](
[registerDate] [datetime] NULL,
[password] [varchar](50) NULL,
[role] [varchar](50) NULL,
[securityQuestion] [varchar](50) NULL,
[securityAnswer] [varchar](50) NULL,
[zipCode] [varchar](50) NULL,
[alternateEmail] [varchar](50) NULL,
[emailAddress] [varchar](50) NULL,
[bday_month] [varchar](50) NULL,
[bday_day] [varchar](50) NULL,
[bday_year] [varchar](50) NULL,
[userID] [int] IDENTITY(1,1) NOT NULL,
[gender] [varchar](50) NULL,
[siteId] [varchar](50) NULL,
[city] [varchar](50) NULL,
[state] [varchar](50) NULL,
[country] [varchar](50) NULL,
[edit_date] [varchar](50) NULL,
[lastName] [varchar](50) NULL,
[firstName] [varchar](50) NULL,
[confirmed] [bit] NULL DEFAULT ((0)),
CONSTRAINT [PK_userID] PRIMARY KEY CLUSTERED
(
[userID] 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

• Next in forum: Re: Pass Table as a parameter to a function
• Prev in forum: Re: Pass Table as a parameter to a function
• Thread view: FK Assignment on Surrogate Key Table Throws Error

[Reply to this message]