locked
Issue creating Foreign Key Relationship RRS feed

  • 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!
    Friday, February 15, 2013 11:35 AM

Answers

  • 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

    • Marked as answer by Iric Wen Monday, February 25, 2013 9:29 AM
    Sunday, February 17, 2013 3:28 PM

All replies

  • 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)
    Friday, February 15, 2013 11:50 AM
  • 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]

    Friday, February 15, 2013 11:54 AM
  • 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, esquel@sommarskog.se
    • Proposed as answer by Naomi N Sunday, February 17, 2013 4:22 PM
    Friday, February 15, 2013 12:15 PM
  • 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

    Friday, February 15, 2013 12:45 PM
  • 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.

    Friday, February 15, 2013 1:13 PM
  • 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

    • Marked as answer by Iric Wen Monday, February 25, 2013 9:29 AM
    Sunday, February 17, 2013 3:28 PM