Error message with FOREIGN KEY REFERENCES

Question

I am trying to create a table that will link to multiple roles. The table is called UserRoles and has only two columns.

The first column contains a reference to a user The second column contains a link to a role

I used FOREIGN KEY REFERENCES as you may notice at the bottom but I keep getting the error message

The following error occured while executing the query:

Server: Msg 1770, Level 16, State 0, Line 1 Foreign key 'FK_UserRoles_user___70DDC3D8' references invalid column 'user_id' in referenced table 'MyUsers'.

Could not create constraint. See previous errors.

Is there a better way to do this?

CREATE TABLE MyUsers
(
id                INT          IDENTITY(1,1)PRIMARY KEY,
user_logon_id     VARCHAR(30)  NOT NULL,
user_full_name    VARCHAR(30)  NULL,
user_description  VARCHAR(125) NULL,
user_password     VARCHAR(125) NOT NULL,
);

INSERT INTO MyUsers (user_logon_id, user_full_name, user_description, user_password) VALUES ('mcobery', 'Marc Cobery',

CREATE TABLE MyRole
(
myrole_id         INT          IDENTITY(1,1)PRIMARY KEY,
role_name         VARCHAR(30)  NOT NULL,
role_description  VARCHAR(50)  NULL,
);

INSERT INTO MyRole (role_name, role_description) VALUES ('administrator', ' Administrator of the web site');

INSERT INTO MyRole (role_name, role_description) VALUES ('user', ' User of the web site');

CREATE TABLE UserRoles
(
user_id int FOREIGN KEY REFERENCES MyUsers(user_id),
role_id int FOREIGN KEY REFERENCES MyRole(role_id),
);

Answer 1

It should be like below

CREATE TABLE UserRoles
 (
    user_id int FOREIGN KEY REFERENCES MyUsers(id),
    role_id int FOREIGN KEY REFERENCES MyRole(myrole_id),
 );

user_id column doesn't exist in MyUsers table

Answer 2

Your table should be like below:

CREATE TABLE UserRoles
(
user_id int FOREIGN KEY REFERENCES MyUsers(id),
role_id int FOREIGN KEY REFERENCES MyRole(myrole_id),
);

Here is the rule while creating referernces:

Column_Name DATATYPE FOREIGN KEY REFERENCES ParentTableName(parentColumnName)