Error 1785 when creating FK constraint

andie_cc · September 28, 2019, 7:58am

Hi Everyone,

I am currently creating a database for a university quick assignment. So, I thought I understood what caused de error 1785, nontheless I´ve come to a confusing end.

Here´s my DB as it is now:

Sorry names of the tables are in spanish, but the thing is:

my tables DETALLE_PEDIDO and ENVIOS have the Id_Pedido from the table PEDIDO as a foreign key and I had no trouble entering that constraint. (On delete cascade on update cascade).

Now, I am asked to create a new table called CIUDAD(CITY), with columns City_Id and City_Name.
Then, eliminate the column Ciudad from the tables ALMACENES and CLIENTES and add a new column called City_ID.

So, I created the table successfully. I have added the column City_ID on both ALMANCENES and CLIENTES and eliminated the column called Ciudad on each of these as well.

The problem is, I can create the constraint for FK city_id referenced to CLIENTES city_id on only one table. When I try on the 2nd table I get the error 1785. But ... how come it did let me do it with the Id_Pedido???

harishgg1 · September 28, 2019, 8:04am

hi

hope these links help

andie_cc · September 28, 2019, 8:07am

Hi,

Thank you so much for your prompt response. I have actually read both of the link you sent already. I understand the error and how to resolve it in fact. But what has given me a headache is understanding,

why does the error only comes up when trying to add the constraint on the tables ALMACENES and CLIENTES but it did not happen when I entered the same type of constraint on the tables ENVIOS and DETALLE_PEDIDO?

harishgg1 · September 28, 2019, 8:08am

one possibility is the DATA in the tables

andie_cc · September 28, 2019, 8:09am

I have not added any Data on the tables just yet. Would it be the data types when I created the columns?

harishgg1 · September 28, 2019, 8:10am

yes that also .. data types have to match

andie_cc · September 28, 2019, 8:22am

The data types match for sure. I have checked and rechecked. But here´s the deal:

The red ones, I was ale to add the following:

ALTER TABLE DETALLE_PEDIDO
ADD CONSTRAINT FK1_DETALLE_PEDIDO FOREIGN KEY (Id_Pedido)
REFERENCES PEDIDO (Id_Pedido)
ON DELETE CASCADE
ON UPDATE CASCADE
;

ALTER TABLE ENVIOS
ADD CONSTRAINT FK1_ENVIOS FOREIGN KEY (Id_Pedido)
REFERENCES PEDIDO (Id_Pedido)
ON DELETE CASCADE
ON UPDATE CASCADE
;

But when I try with the yellow ones, I get the error.

andie_cc · September 28, 2019, 8:33am

So my question and confusion is not, why am I getting the error when adding this:

alter table ALMACENES
ADD CONSTRAINT FK_ALMACENES FOREIGN KEY (Id_Ciudad)
REFERENCES CIUDAD (Id_Ciudad)
ON DELETE CASCADE
ON UPDATE CASCADE
;

alter table CLIENTES
ADD CONSTRAINT FK_CLIENTES FOREIGN KEY (Id_Ciudad)
REFERENCES CIUDAD (Id_Ciudad)
ON DELETE CASCADE
ON UPDATE CASCADE
;

my questions is, "Why did NOT get the error when I added this'"

ALTER TABLE DETALLE_PEDIDO
ADD CONSTRAINT FK1_DETALLE_PEDIDO FOREIGN KEY (Id_Pedido)
REFERENCES PEDIDO (Id_Pedido)
ON DELETE CASCADE
ON UPDATE CASCADE
;

ALTER TABLE ENVIOS
ADD CONSTRAINT FK1_ENVIOS FOREIGN KEY (Id_Pedido)
REFERENCES PEDIDO (Id_Pedido)
ON DELETE CASCADE
ON UPDATE CASCADE
;

harishgg1 · September 28, 2019, 8:49am

hi

please send me your database CREATE Script with all the TABLES create script

I will try and figure out why !!!

andie_cc · September 28, 2019, 9:15am

Hi @harishgg1 thaaaank you thank you so much for your help. It happens to be, after breaking my head for hours, I just found out why. But I appreciate your willingness. As a matter of fact it is because:

The table CIUDAD when linked to ALMACENES will modify other tables as ALMACENES has dependencies. And when linked to CLIENTES at the same time, will modify other tables as CLIENTES has other dependencies and that is where the double path is.

While on PEDIDO, the tables with Id_Pedido as FK don´t have any further dependencies so there is no double path, only two tables being modified. Just in case you are as curious as I am, here´s my code: (the one that fails)

create database PEDIDOS;
use PEDIDOS;

create table CLIENTES
(
Id_Cliente int not null,
Nombre_Cliente varchar (50) not null,
Ciudad_Cliente varchar (30) not null,
Telefono_Cliente char (10),
);

create table PEDIDO
(
Id_Pedido int not null,
Fecha_Pedido datetime not null,
ID_Cliente int not null,
Total_Importe_Pedido float not null,
);

create table DETALLE_PEDIDO
(
Id_Pedido int not null,
Id_Articulo int not null,
Cantidad_DP int not null,
);

create table ARTICULOS
(
Id_Articulo int not null,
Nombre_Articulo varchar (50) not null,
Precio_UNI_Articulo money not null,
);

create table ENVIOS
(
Id_Pedido int not null,
Id_Almacen int not null,
Fecha_Envio datetime not null,
);

create table ALMACENES
(
Id_Almacen int not null,
Ciudad_Almacen varchar (30) not null,
);

alter table CLIENTES
add constraint pk_CLIENTES primary key (Id_Cliente);

alter table PEDIDO
add constraint pk_PEDIDO primary key (Id_Pedido);

alter table DETALLE_PEDIDO
add constraint pk_DETALLE_PEDIDO primary key (Id_Pedido, Id_Articulo);

alter table ARTICULOS
add constraint pk_ARTICULOS primary key (Id_Articulo);

alter table ENVIOS
add constraint pk_ENVIOS primary key (Id_Pedido,Id_Almacen);

alter table ALMACENES
add constraint pk_ALMACENES primary key (Id_Almacen);

ALTER TABLE PEDIDO
ADD CONSTRAINT FK_PEDIDO FOREIGN KEY (Id_Cliente)
REFERENCES CLIENTES (Id_Cliente)
ON DELETE CASCADE
ON UPDATE CASCADE
;

ALTER TABLE DETALLE_PEDIDO
ADD CONSTRAINT FK1_DETALLE_PEDIDO FOREIGN KEY (Id_Pedido)
REFERENCES PEDIDO (Id_Pedido)
ON DELETE CASCADE
ON UPDATE CASCADE
;

ALTER TABLE DETALLE_PEDIDO
ADD CONSTRAINT FK2_DETALLE_PEDIDO FOREIGN KEY (Id_Articulo)
REFERENCES ARTICULOS (Id_Articulo)
ON DELETE CASCADE
ON UPDATE CASCADE
;

ALTER TABLE ENVIOS
ADD CONSTRAINT FK1_ENVIOS FOREIGN KEY (Id_Pedido)
REFERENCES PEDIDO (Id_Pedido)
ON DELETE CASCADE
ON UPDATE CASCADE
;

ALTER TABLE ENVIOS
ADD CONSTRAINT FK2_ENVIOS FOREIGN KEY (Id_Almacen)
REFERENCES ALMACENES (Id_Almacen)
ON DELETE CASCADE
ON UPDATE CASCADE
;

alter table ARTICULOS
ADD Existencia_Articulo int;

alter table CLIENTES
alter column Telefono_Cliente char(14);

alter table PEDIDO
alter column Total_Importe_Pedido money not null;

alter table ALMACENES add
Nombre_Almacen varchar (50) not null,
Total_Empleados_Almacen int
;

alter table ARTICULOS
add Costo_Articulo money not null;
EXEC sp_rename 'ARTICULOS.Precio_UNI_Articulo', 'Precio_Articulo', 'COLUMN';

create table CIUDAD
(
Id_Ciudad int not null,
Nombre_Ciudad varchar (50) not null,
constraint pk_CIUDAD primary key (Id_Ciudad),
);

alter table ALMACENES
ADD CONSTRAINT FK_ALMACENES FOREIGN KEY (Id_Ciudad)
REFERENCES CIUDAD (Id_Ciudad)
ON DELETE CASCADE
ON UPDATE CASCADE
;

alter table CLIENTES
ADD CONSTRAINT FK_CLIENTES FOREIGN KEY (Id_Ciudad)
REFERENCES CIUDAD (Id_Ciudad)
ON DELETE CASCADE
ON UPDATE CASCADE
;