Foreign key references invalid table

Please see in this SQLfiddle link, Link

CREATE TABLE NUMBER(
    ID INT PRIMARY KEY, 
    NUMBER VARCHAR(10) NOT NULL,
    NAME VARCHAR(36) NOT NULL REFERENCES USERS(NAME)
);

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.


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
)

Tags:

Sql