Issue creating Foreign Key Relationship

Question

Hi,

I am trying to create a table relationship but get this error... "The columns in table do not match an existing primary key or unique constraint"

My tables:

User Table

UniqueID int PRIMARY KEY IDENTITY,
Username nvarchar(200) FK,
BookID   nvarchar(4)

UserDates Table

Username  nvarchar(200) PRIMARY KEY,
StartDate datetime

username + BookID is a unique key.
There can be multiple BookIDs for a single Username. But only a single entry in UserDates table for teach Username.

e.g.

UniqueID	Username	BookID
1		 John		 a12f
2		 John		 b23e
3		 John		 b123

Username	StartDate
John		 2013/01/28

Any ideas why cannot create FK relationship? Thanks!

Answer 1

A Foreign Key has to relate to either a Primary Key (Single column or multiple in a Composite Key senario)... Or to a Unique Constraint... (Single column or multiple composite keys)

Answer 2

Most likely the table where you are adding the fk contraint is already populated and it has a value that does not exist in [User Table] or [User Dates]

Answer 3

Are you trying?

   ALTER TABLE UserDates ADD
     CONSTRAINT fk_UserDates_UserTable FOREIGN KEY(Username)
        REFERENCES User (Username)

This is not permitted. An FK must have exactly the same columns as the key in the referenced tables, even though there occasionally scenarios where this makes sense. But there is also a considerable chance that this an indication of that your database design is flawed.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 4

In my UserDates table I was able to create a FK onto itself...So UserDates.Username = UserDates.Username

This seems to work oddly enough, and allowed my to create a table relationship with User table

Answer 5

I would suggest to change your table structure as below

User Table

UserID Primary Key

UserName

USerBooks Table

UserID FK to UserTable UserID

BookID (Hopefully Book will have a separate master table)

UserDates

UserID FK to UserTable UserId

Startdate

---

Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

Answer 6

Consider the following design:

CREATE TABLE [User] (
UserID INT IDENTITY(1,1) Primary Key, 
UserName nvarchar(50),
UserEmail varchar(70) UNIQUE,
ModifiedDate date default CURRENT_TIMESTAMP);

CREATE TABLE Book (
BookID INT IDENTITY(1,1) Primary Key, 
Author nvarchar(50),
Title varchar(120),
UNIQUE (Author,Title),
ModifiedDate date default CURRENT_TIMESTAMP);


CREATE TABLE UserBooks (
UserID INT REFERENCES [User],
BookID INT REFERENCES Book,
Startdate datetime not null default CURRENT_TIMESTAMP,
PRIMARY KEY (UserID, BookID));

---

Kalman Toth Database & OLAP Architect
Paperback / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012