Foreign key references invalid table

Question

I have the following code:

create table test.dbo.Users
(
    Id int identity(1,1) primary key,
    Name varchar(36) not null
)

create table test.dbo.Number
(
    Id int identity(1,1) primary key,
    Number varchar(10) not null,
    Name varchar(36) not null foreign key references Users.Name
)

The foreign key throws an error saying Foreign key 'FK__Number__Name__1CF15040' references invalid table 'Users.Name'..

What did I do wrong?

Answer 1

Foreign key must reference a primary key in another table

I would use the following code

I hope it is useful

use test
create table test.dbo.Users
(
    Id int identity(1,1) primary key,
    Name varchar(36) not null
)

create table test.dbo.Number
(
    Id int identity(1,1) primary key,
    Number varchar(10) not null,
    Users_Id int not null

    constraint fk_Number_Users foreign key (Users_Id) 
               references Users(Id)
               on update no action
               on delete no action
)

Answer 2

You should reference the Primary Key of test.dbo.users.

In SQL Server you could do this:

create table Number
(
    Id int identity(1,1) primary key,
    Number varchar(10) not null,
    Name varchar(36) not null ,
    Id_FK int not null foreign key references Users(id)
)

In the above, you have a mandatory association between the 2 tables. If you want to have optional relationship, remove the 'not null' from Id_Fk....

Note: I don't know what is the Name column for.