SQL Help

[CookieMaster]

Hi, So I'm getting this error,

 

There are no primary or candidate keys in the referenced table that match the referencing column list in the foreign key

 

My tables are as follows. 

CREATE TABLE AUTHORS (
  AuthorID INT NOT NULL,
  AuthorFirstName VARCHAR(45) NULL,
  AuthorLastName VARCHAR(45) NULL,
  AuthorCountry VARCHAR(45) NULL,
  PUBLISHERS_PublisherID INT NOT NULL,
  PRIMARY KEY (AuthorID, PUBLISHERS_PublisherID),
  INDEX fk_AUTHORS_PUBLISHERS1_idx (PUBLISHERS_PublisherID ASC),
  CONSTRAINT fk_AUTHORS_PUBLISHERS1
  FOREIGN KEY (PUBLISHERS_PublisherID)
  REFERENCES dbo.PUBLISHERS (PublisherID))

That table has no errors.

The following table has the error mentioned above at REFERENCES AUTHORS (AuthorID)

 

CREATE TABLE BOOKS (
ISBN INT NOT NULL,
  SeriesName VARCHAR(45) NULL,
  Title VARCHAR(45) NULL,
  AuthorID INT NULL,
  PublicationDate DATE NULL,
  Publisher VARCHAR(45) NULL,
  AUTHORS_AuthorID INT NOT NULL,
  Series_SeriesName VARCHAR(45) NOT NULL,
  Series_AuthorID INT NOT NULL,
  PRIMARY KEY (ISBN, AUTHORS_AuthorID, Series_SeriesName, Series_AuthorID),
  CONSTRAINT fk_BOOKS_AUTHORS1
    FOREIGN KEY (AUTHORS_AuthorID)
    REFERENCES AUTHORS (AuthorID)
  CONSTRAINT fk_BOOKS_Series1
    FOREIGN KEY (Series_SeriesName , Series_AuthorID)
    REFERENCES SERIES (SeriesName , AuthorID))

Not really experienced at all, although stuck with this, so hopefully I can get an answer, Thank you very much.

 

BTW this was auto generated through sql workbench but it contained some other stuff because this is going from mysql to sql server. 

[nerdv2]

Does the AUTHORS table and the AuthorID column exist? If so, does the AuthorID are set as primary key?

[CookieMaster]

23 minutes ago, nerdv2 said:

Does the AUTHORS table and the AuthorID column exist? If so, does the AuthorID are set as primary key?

Yes, I put in the authors table above. And yes authorid is the pk

[nerdv2]

31 minutes ago, CookieMaster said:

Yes, I put in the authors table above. And yes authorid is the pk

Hmm, that's weird. I tested the queries with the exception of publisher and series table and works without issues. Where do you try to execute this?

[CookieMaster]

1 minute ago, nerdv2 said:

Hmm, that's weird. I tested the queries with the exception of publisher and series table and works without issues. Where do you try to execute this?

Microsoft SQL Server Management Studio 2017 (SSMS)

[nerdv2]

Do you have a complete error log of that? I use Navicat for testing since I am running linux.

[CookieMaster]

4 minutes ago, nerdv2 said:

Do you have a complete error log of that? I use Navicat for testing since I am running linux.

I think i can get that, btw here's the error. 

https://imgur.com/a/4UEXz

Msg 1776, Level 16, State 0, Line 1
There are no primary or candidate keys in the referenced table 'AUTHORS' that match the referencing column list in the foreign key 'fk_BOOKS_AUTHORS1'.
 

[nerdv2]

How about replacing

REFERENCES AUTHORS

with

REFERENCES dbo.AUTHORS

 

[CookieMaster]

1 minute ago, nerdv2 said:

How about replacing

REFERENCES AUTHORS

with

REFERENCES dbo.AUTHORS

 

Nope  didn't work

[CookieMaster]

Here's what mysql workbench generated through reverse engineering the erd diagram 

 

-- -----------------------------------------------------
-- Table `mydb`.`AUTHORS`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`AUTHORS` (
  `AuthorID` INT NOT NULL,
  `AuthorFirstName` VARCHAR(45) NULL,
  `AuthorLastName` VARCHAR(45) NULL,
  `AuthorCountry` VARCHAR(45) NULL,
  `PUBLISHERS_PublisherID` INT NOT NULL,
  PRIMARY KEY (`AuthorID`, `PUBLISHERS_PublisherID`),
  CONSTRAINT `fk_AUTHORS_PUBLISHERS1`
    FOREIGN KEY (`PUBLISHERS_PublisherID`)
    REFERENCES `mydb`.`PUBLISHERS` (`PublisherID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`BOOKS`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`BOOKS` (
  `ISBN` INT NOT NULL,
  `SeriesName` VARCHAR(45) NULL,
  `Title` VARCHAR(45) NULL,
  `AuthorID` INT NULL,
  `PublicationDate` DATE NULL,
  `Publisher` VARCHAR(45) NULL,
  `AUTHORS_AuthorID` INT NOT NULL,
  `Series_SeriesName` VARCHAR(45) NOT NULL,
  `Series_AuthorID` INT NOT NULL,
  PRIMARY KEY (`ISBN`, `AUTHORS_AuthorID`, `Series_SeriesName`, `Series_AuthorID`),
  CONSTRAINT `fk_BOOKS_AUTHORS1`
    FOREIGN KEY (`AUTHORS_AuthorID`)
    REFERENCES `mydb`.`AUTHORS` (`AuthorID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_BOOKS_Series1`
    FOREIGN KEY (`Series_SeriesName` , `Series_AuthorID`)
    REFERENCES `mydb`.`Series` (`SeriesName` , `AuthorID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

 

[RockiLocky]

3 hours ago, CookieMaster said:

Here's what mysql workbench generated through reverse engineering the erd diagram 

 

-- -----------------------------------------------------
-- Table `mydb`.`AUTHORS`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`AUTHORS` (
  `AuthorID` INT NOT NULL,
  `AuthorFirstName` VARCHAR(45) NULL,
  `AuthorLastName` VARCHAR(45) NULL,
  `AuthorCountry` VARCHAR(45) NULL,
  `PUBLISHERS_PublisherID` INT NOT NULL,
  PRIMARY KEY (`AuthorID`, `PUBLISHERS_PublisherID`),
  CONSTRAINT `fk_AUTHORS_PUBLISHERS1`
    FOREIGN KEY (`PUBLISHERS_PublisherID`)
    REFERENCES `mydb`.`PUBLISHERS` (`PublisherID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`BOOKS`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`BOOKS` (
  `ISBN` INT NOT NULL,
  `SeriesName` VARCHAR(45) NULL,
  `Title` VARCHAR(45) NULL,
  `AuthorID` INT NULL,
  `PublicationDate` DATE NULL,
  `Publisher` VARCHAR(45) NULL,
  `AUTHORS_AuthorID` INT NOT NULL,
  `Series_SeriesName` VARCHAR(45) NOT NULL,
  `Series_AuthorID` INT NOT NULL,
  PRIMARY KEY (`ISBN`, `AUTHORS_AuthorID`, `Series_SeriesName`, `Series_AuthorID`),
  CONSTRAINT `fk_BOOKS_AUTHORS1`
    FOREIGN KEY (`AUTHORS_AuthorID`)
    REFERENCES `mydb`.`AUTHORS` (`AuthorID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_BOOKS_Series1`
    FOREIGN KEY (`Series_SeriesName` , `Series_AuthorID`)
    REFERENCES `mydb`.`Series` (`SeriesName` , `AuthorID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

 

Could you get me a pic of your tables themself? and is the above code the code you execute through your program?

[RockiLocky]

6 hours ago, CookieMaster said:

Hi, So I'm getting this error,

 

There are no primary or candidate keys in the referenced table that match the referencing column list in the foreign key

 

My tables are as follows. 

CREATE TABLE AUTHORS (
  AuthorID INT NOT NULL,
  AuthorFirstName VARCHAR(45) NULL,
  AuthorLastName VARCHAR(45) NULL,
  AuthorCountry VARCHAR(45) NULL,
  PUBLISHERS_PublisherID INT NOT NULL,
  PRIMARY KEY (AuthorID, PUBLISHERS_PublisherID),
  INDEX fk_AUTHORS_PUBLISHERS1_idx (PUBLISHERS_PublisherID ASC),
  CONSTRAINT fk_AUTHORS_PUBLISHERS1
  FOREIGN KEY (PUBLISHERS_PublisherID)
  REFERENCES dbo.PUBLISHERS (PublisherID))

That table has no errors.

The following table has the error mentioned above at REFERENCES AUTHORS (AuthorID)

 

CREATE TABLE BOOKS (
ISBN INT NOT NULL,
  SeriesName VARCHAR(45) NULL,
  Title VARCHAR(45) NULL,
  AuthorID INT NULL,
  PublicationDate DATE NULL,
  Publisher VARCHAR(45) NULL,
  AUTHORS_AuthorID INT NOT NULL,
  Series_SeriesName VARCHAR(45) NOT NULL,
  Series_AuthorID INT NOT NULL,
  PRIMARY KEY (ISBN, AUTHORS_AuthorID, Series_SeriesName, Series_AuthorID),
  CONSTRAINT fk_BOOKS_AUTHORS1
    FOREIGN KEY (AUTHORS_AuthorID)
    REFERENCES AUTHORS (AuthorID)
  CONSTRAINT fk_BOOKS_Series1
    FOREIGN KEY (Series_SeriesName , Series_AuthorID)
    REFERENCES SERIES (SeriesName , AuthorID))

Not really experienced at all, although stuck with this, so hopefully I can get an answer, Thank you very much.

 

BTW this was auto generated through sql workbench but it contained some other stuff because this is going from mysql to sql server. 

https://stackoverflow.com/questions/17879735/there-are-no-primary-or-candidate-keys-in-the-referenced-table-that-match-the-re

This might actually help you out. The guy seems to have the kinda same problem

 

[CookieMaster]

6 hours ago, RockiLocky said:

Could you get me a pic of your tables themself? and is the above code the code you execute through your program?

Yes I'm executing the code through the program, first the Author Table However, then I try the books table and i get that error.

[RockiLocky]

10 hours ago, CookieMaster said:

Yes I'm executing the code through the program, first the Author Table However, then I try the books table and i get that error.

Did you check out the link I posted?

[Dan Watling]

The problem is that the "AuthorID" field referenced in the constraint is not guaranteed to be unique. In your create statement above, the PK for the AUTHORS table is AuthorID + PublisherID. SQL Server requires foreign keys reference unique keys in the target table (i.e. primary key or a unique index). You have three options here: 1) Drop the PublisherID from the AUTHORS table PrimaryKey, 2) Create a UNIQUE constraint on the AUTHORS.AuthorID field, or 3) Add the PublisherID to the BOOKS table and reference it in the foreign key constraint.

 

-Dan