Problem creating a Foreign key

Asked

Viewed 905 times

1

I created a table tbl_cond with a PRIMARY KEY calling for ID_cond

When creating a second table with a FOREIGN KEY, being her ID_cond of tbl_cond, put the following code. 

use db_clientes
create table tbl_proprietarios
(ID_proprietario SMALLINT PRIMARY KEY IDENTITY(1,1),
name varchar(50) not null,
email varchar(50) not null,
constraint ID_cond foreign key (ID_cond) 
references tbl_cond(ID_cond)
)

When I give F5 to create the table, the following error message appears:

"Message 1769, Level 16, State 1, Line 2 The foreign key id_cond references the invalid column id_cond in the table of reference 'tbl_proprietarios'. Message 1750, Level 16, Status 0, Line 2 Could not create constraint or index. See previous mistakes."

I tried some options, but I could not visualize a way out for this problem.

sql sql-server sql-server-2014

  • Create an Id_cond field in your tbl_proprietary table. When creating the Constraint you are saying that this field, which should exist, references a row of table tbl_cond with same content.

2 answers

2

You are creating a Constraint with a column that does not exist, you need to create the field in the table tbl_proprietarios to refer to Foreign Key.

create table tbl_proprietarios
(
    ID_proprietario SMALLINT PRIMARY KEY IDENTITY(1,1),
    name varchar(50) not null,
    email varchar(50) not null,
    id_condicao int,
    CONSTRAINT FK_id_condicao FOREIGN KEY (id_condicao)
    REFERENCES tbl_cond(ID_cond)
)

2

Apparently, the problem says that the column of your table tbl_proprietarios is invalid. I believe you did not create the attribute at the time of table creation: 

use db_clientes
create table tbl_proprietarios
(
ID_proprietario SMALLINT PRIMARY KEY IDENTITY(1,1),
name varchar(50) not null,
email varchar(50) not null,
ID_cond SMALLINT NOT NULL, --Faltou criar o atributo nessa tabela
constraint ID_cond foreign key (ID_cond) 
references tbl_cond(ID_cond)
)

I would recommend the following also:

  • Check column names in the command (case sensitive);
  • Do not put the name of constraint with the same name as the columns;
  • Check if attributes contain the same type.

Browser other questions tagged sql sql-server sql-server-2014

You are not signed in. Login or sign up in order to post.