There are no primary or candidate keys in the referenced table, but i have PK on previous table-CodePudding

I'm getting this error:

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

CREATE TABLE Users 
(
    AccountID int NOT NULL,
    Username NCHAR(16) NOT NULL,
    "E-mail" NCHAR(20) NOT NULL,
    Password NCHAR(20),
    BirthDate DATE,
    PhoneNumber NCHAR(15),
    FirstName CHAR(20),
    LastName CHAR(20)

    PRIMARY KEY (AccountID, Username)
);

CREATE TABLE Stream 
(
    StreamID int NOT NULL,
    AccountID int NOT NULL,
    FOREIGN KEY (AccountID) REFERENCES Users(AccountID),
    "Name" CHAR(10) NOT NULL,
    Username NCHAR(16) NOT NULL,
    FOREIGN KEY (Username) REFERENCES Users(Username),
    Description NCHAR(50),
    Chat TEXT,
    StreamCategory NCHAR(30),
    StreamApp NCHAR(20)

    PRIMARY KEY (StreamID, Username, AccountID)
);

I'm getting the error on the line

FOREIGN KEY (AccountID) REFERENCES Users(AccountID)

and on the line

FOREIGN KEY (Username) REFERENCES Users(Username)

Could someone please tell me whats happening here and how I can fix it?

I have primary key for Users.Username and Users.AccountID, and still get this error.

CodePudding user response：

If you have a composite key, you need to reference it as a composite, too - in your code you're referencing the separate columns. So, instead of

FOREIGN KEY (AccountID) REFERENCES Users(AccountID),
...
FOREIGN KEY (Username) REFERENCES Users(Username),

FOREIGN KEY (AccountID, Username) REFERENCES Users(AccountID, Username),

P.S. Based on your column names, I would recommend you to think twice if you really need the composite key on Users table. Usually the ID of the user in the application needs to be unique on its own, but your design doesn't guarantee it:

INSERT INTO Users(AccountID, Username, "E-Mail") VALUES(1,"Alice","[email protected]");
INSERT INTO Users(AccountID, Username, "E-Mail") VALUES(1,"Bob","[email protected]");
INSERT INTO Users(AccountID, Username, "E-Mail") VALUES(2,"Alice","[email protected]");

- this will succeed and you will get several users with the same Username but different AccountIDs and with the same AccountID, but different Usernames. I'm not sure if this is what you want.