Strange problem in SQL

pstnc · #1 (**permalink**) 01-09-12, 09:15

Hello everybody

Today i registered to ask a strange situacion that happened to me with a very simple querys., so i want to build the same schema of a litlle database like in the map image, i create manually the all tables and all columns with data (see the attach New DB), but right now is imposible to me to add the primaray keys like in atached image!!

for example
CONSTRAINT PEDIDOS_FOREIGN_KEY FOREIGN KEY (ID_CLIENTE) REFERENCES CLIENTES(ID_CLIENTE),
CONSTRAINT PEDIDOS_FOREIGN_KEY FOREIGN KEY (ID_EMPLEADO) REFERENCES EMPLEADOS(ID_EMPLEADO))

and is not working., he give me the error

ORA-02264: name already used by an existing constraint

but where!! i make a drop to all tables 10 times today trying to make the conexion with PK and FK and he steel give me the same error!!

is a bug., or what?? because i make everything ok.

i put the code of all tables if is more easy

Code:

CREATE TABLE CATEGORIAS
(
ID_CATEGORIA        INTEGER,
NOMBRE_CATEGORIA    VARCHAR2(100 CHAR),
DESCRIPCION         VARCHAR2(100 CHAR) 
);


CREATE TABLE CLIENTES
(
ID_CLIENTE        INTEGER,
NOMBRE_COMPANIA   VARCHAR2(50 CHAR),
NOMBRE_CONTACTO   VARCHAR2(100 CHAR),
CARGO_CONTACTO    VARCHAR2(30 CHAR),
DIRECCION         VARCHAR2(80 CHAR),
CIUDAD            VARCHAR2(20 CHAR),
REGION            VARCHAR2(100 CHAR),
COD_POSTAL        INTEGER,
PAIS              VARCHAR2(20 CHAR),
TELEFONO          INTEGER,
FAX               INTEGER
);


create table COMPANIAS_DE_ENVIOS
(
ID_COMPANIA_ENVIOS      INTEGER,
NOMBRE_COMPANIA         VARCHAR2(50 CHAR),
TELEFONO                INTEGER
);


CREATE TABLE DETALLES_DE_PEDIDO
(
ID_PEDIDO         INTEGER,
ID_PRODUCTO       INTEGER,
PRECIO_UNIDAD     INTEGER,
CANTIDAD          INTEGER,
DESCUENTO         INTEGER
);


CREATE TABLE EMPLEADOS
(
ID_EMPLEADO          INTEGER,
APELLIDOS            VARCHAR2(50 CHAR),   
NOMBRE               VARCHAR2(50 CHAR),
CARGO                INTEGER,
TRATAMIENTO          VARCHAR2(20 CHAR),
FECHA_NACIMIENTO     DATE,
FECHA_CONTRATACION   DATE,
DIRECCION            VARCHAR2(50 CHAR),
CIUDAD               VARCHAR2(30 CHAR),
REGION               VARCHAR2(100 CHAR),
COD_POSTAL           INTEGER,
PAIS                 VARCHAR2(20 CHAR)
);


CREATE TABLE PEDIDOS
(
ID_PEDIDO                    INTEGER,
ID_CLIENTE                   INTEGER,
ID_EMPLEADO                  INTEGER,
FECHA_PEDIDO                 DATE,
FECHA_ENTREGA                DATE,
FECHA_ENVIO                  DATE,
FORMA_ENVIO                  VARCHAR2(20 CHAR),
CARGO                        INTEGER,
DESTINATARIO                 VARCHAR2(100 CHAR),
DIRECCION_DESTINATARIO       VARCHAR2(50 CHAR),
CIUDAD_DESTINATARIO          VARCHAR2(20 CHAR),
REGION_DESTINATARIO          VARCHAR2(30 CHAR)
);


CREATE TABLE PRODUCTOS
(
ID_PRODUCTO            INTEGER,
NOMBRE_PRODUCTO        VARCHAR2(50 CHAR),
ID_PROVEEDOR           INTEGER,
ID_CATEGORIA           INTEGER,
CANTITAD_POR_UNIDAD    INTEGER,
PRECIO_UNIDAD          INTEGER,
UNIDADES_EN_EXISTENCIA INTEGER
); 


CREATE TABLE PROVEEDORES
(
ID_PROVEEDOR         INTEGER,
NOMBRE_COMPANIA      VARCHAR2(50 CHAR),
NOMBRE_CONTACTO      VARCHAR2(80 CHAR),
CARGO_CONTACTO       VARCHAR2(40 CHAR),
DIRECCION            VARCHAR2(100 CHAR),
CIUDAD               VARCHAR2(30 CHAR),
REGION               VARCHAR2(100 CHAR),
COD_POSTAL           INTEGER,
PAIS                 VARCHAR2(10 CHAR),
TELEFONO             INTEGER,
FAX                  INTEGER,
PAGINA_PRINCIPAL     VARCHAR2(30 CHAR)
)

shammat · #2 (**permalink**) 01-09-12, 09:22

Quote:

Originally Posted by pstnc

ORA-02264: name already used by an existing constraint

but where!!

Right there:

CONSTRAINT PEDIDOS_FOREIGN_KEY FOREIGN KEY (ID_CLIENTE) REFERENCES CLIENTES(ID_CLIENTE),
CONSTRAINT PEDIDOS_FOREIGN_KEY FOREIGN KEY (ID_EMPLEADO) REFERENCES EMPLEADOS(ID_EMPLEADO))

You are creating two constraints with the same name.
You have to use two different names if you create two constraints.

pstnc · #3 (**permalink**) 01-09-12, 09:40

Quote:

Originally Posted by shammat

Right there:

CONSTRAINT PEDIDOS_FOREIGN_KEY FOREIGN KEY (ID_CLIENTE) REFERENCES CLIENTES(ID_CLIENTE),
CONSTRAINT PEDIDOS_FOREIGN_KEY FOREIGN KEY (ID_EMPLEADO) REFERENCES EMPLEADOS(ID_EMPLEADO))

You are creating two constraints with the same name.
You have to use two different names if you create two constraints.

False ., because if you see the attach image ID_CLIENTE is from Clientes table and ID_EMPLEADO is from Empleados table.

the only word that i repeat is PEDIDOS but this is the table where i need to define the 2 constraints (id_cliente and id_empleado).,and the constraint query is correct!

shammat · #4 (**permalink**) 01-09-12, 09:50

Quote:

Originally Posted by pstnc

False ., because if you see the attach image ID_CLIENTE is from Clientes table and ID_EMPLEADO is from Empleados table.

The IDs might be from different tables but you are supplying the same name for both constraints (that's why the error message says: "name already used by an existing constraint")

Quote:

the only word that i repeat is PEDIDOS but this is the table where i need to define the 2 constraints (id_cliente and id_empleado).,and the constraint query is correct!

No you repeat the name of the constraint (PEDIDOS_FOREIGN_KEY)

In the attached file there are no constraint definitions whatsoever and the two lines you have shown us in your initial post repeat the same name for two different constraints.

flyboy · #5 (**permalink**) 01-09-12, 09:51

Quote:

Originally Posted by pstnc

False ., because if you see the attach image ID_CLIENTE is from Clientes table and ID_EMPLEADO is from Empleados table.

the only word that i repeat is PEDIDOS but this is the table where i need to define the 2 constraints (id_cliente and id_empleado).,and the constraint query is correct!

False. The constraint name has to be different, but you are talking about referenced master table and columns in both child/master tables. Oracle seems to be the same opinion as shammat too.
Here is one more thread about the same error: Trouble assigning foreign key
Once again: problem is in duplicate constraint name (put into bold by shammat).

If you still do not believe it, please start reading this post from the beginning.

pstnc · #6 (**permalink**) 01-09-12, 09:55

Ok thank you guys., i am gone read the another thread and then i come with a answer., and happy new year!

EDIT Later

Well., I steel confuze, because in this thread Ora-02264: one expert guy (i think that is a guru for the number of messages) give the next resolution

Code:

CONSTRAINT EPSIODE_FOREIGN_KEY FOREIGN KEY (PA_NUM) REFERENCES PERSON(PA_NUM),
CONSTRAINT EPSIODE_FOREIGN_KEY FOREIGN KEY (RM_CODE) REFERENCES ROOM(RM_CODE),
CONSTRAINT EPSIODE_FOREIGN_KEY FOREIGN KEY (SP_CODE) REFERENCES SPECIALITY(SP_CODE),
CONSTRAINT EPSIODE_FOREIGN_KEY FOREIGN KEY (CP_GMCNUM) REFERENCES CAREPROVIDER(CP_GMCNUM),
CONSTRAINT EPSIODE_FOREIGN_KEY FOREIGN KEY (BL_CODE) REFERENCES BILLING(BL_CODE)

and mine is

Code:

CONSTRAINT PEDIDOS_FOREIGN_KEY FOREIGN KEY (ID_CLIENTE) REFERENCES CLIENTES(ID_CLIENTE),
CONSTRAINT PEDIDOS_FOREIGN_KEY FOREIGN KEY (ID_EMPLEADO) REFERENCES EMPLEADOS(ID_EMPLEADO))

so he repeat EPISODE each time .. EPISODE_FOREIGN_KEY

..

here i put the entire code., please pay attencion also to the map image

Code:

create table COMPANIAS_DE_ENVIOS
(
ID_COMPANIA_ENVIOS      INTEGER PRIMARY KEY,
NOMBRE_COMPANIA         VARCHAR2(50 CHAR) NOT NULL,
TELEFONO                INTEGER
)


CREATE TABLE PEDIDOS
(
ID_PEDIDO                    INTEGER PRIMARY KEY,
ID_CLIENTE                   INTEGER NOT NULL,
ID_EMPLEADO                  INTEGER,
FECHA_PEDIDO                 DATE,
FECHA_ENTREGA                DATE,
FECHA_ENVIO                  DATE,
FORMA_ENVIO                  VARCHAR2(20 CHAR),
CARGO                        INTEGER,
DESTINATARIO                 VARCHAR2(100 CHAR),
DIRECCION_DESTINATARIO       VARCHAR2(50 CHAR),
CIUDAD_DESTINATARIO          VARCHAR2(20 CHAR),
REGION_DESTINATARIO          VARCHAR2(30 CHAR),
CONSTRAINT PEDIDOS_FOREIGN_KEY FOREIGN KEY (ID_CLIENTE) REFERENCES CLIENTES(ID_CLIENTE),
CONSTRAINT PEDIDOS_FOREIGN_KEY FOREIGN KEY (ID_EMPLEADO) REFERENCES EMPLEADOS(ID_EMPLEADO))

select id_cliente from clientes

CREATE TABLE EMPLEADOS
(
ID_EMPLEADO          INTEGER PRIMARY KEY,
APELLIDOS            VARCHAR2(50 CHAR) NOT NULL,   
NOMBRE               VARCHAR2(50 CHAR),
CARGO                INTEGER,
TRATAMIENTO          VARCHAR2(20 CHAR),
FECHA_NACIMIENTO     DATE,
FECHA_CONTRATACION   DATE,
DIRECCION            VARCHAR2(50 CHAR),
CIUDAD               VARCHAR2(30 CHAR),
REGION               VARCHAR2(100 CHAR),
COD_POSTAL           INTEGER,
PAIS                 VARCHAR2(20 CHAR)
)


CREATE TABLE PROVEEDORES
(
ID_PROVEEDOR         INTEGER PRIMARY KEY,
NOMBRE_COMPANIA      VARCHAR2(50 CHAR) NOT NULL,
NOMBRE_CONTACTO      VARCHAR2(80 CHAR),
CARGO_CONTACTO       VARCHAR2(40 CHAR),
DIRECCION            VARCHAR2(100 CHAR),
CIUDAD               VARCHAR2(30 CHAR),
REGION               VARCHAR2(100 CHAR),
COD_POSTAL           INTEGER,
PAIS                 VARCHAR2(10 CHAR),
TELEFONO             INTEGER,
FAX                  INTEGER,
PAGINA_PRINCIPAL     VARCHAR2(30 CHAR),
CONSTRAINT PROVEEDORES_FOREIGN_KEY FOREIGN_KEY (NOMBRE_COMPANIA) REFERENCES COMPANIAS_DE_ENVIOS (NOMBRE_COMPANIA),
CONSTRAINT PROVEEDORES_FOREIGN_KEY FOREIGN_KEY (NOMBRE_CONTACTO) REFERENCES CLIENTES(NOMBRE_CONTACTO),
CONSTRAINT PROVEEDORES_FOREIGN_KEY FOREIGN_KEY (CARGO_CONTACTO) REFERENCES CLIENTES (CARGO_CONTACTO)
)


CREATE TABLE CLIENTES
(
ID_CLIENTE        INTEGER PRIMARY KEY,
NOMBRE_COMPANIA   VARCHAR2(50 CHAR) NOT NULL,
NOMBRE_CONTACTO   VARCHAR2(100 CHAR),
CARGO_CONTACTO    VARCHAR2(30 CHAR),
DIRECCION         VARCHAR2(80 CHAR),
CIUDAD            VARCHAR2(20 CHAR),
REGION            VARCHAR2(100 CHAR),
COD_POSTAL        INTEGER,
PAIS              VARCHAR2(20 CHAR),
TELEFONO          INTEGER,
FAX               INTEGER
)  
 

CREATE TABLE DETALLES_DE_PEDIDO
(
ID_PEDIDO         INTEGER primary key,
ID_PRODUCTO       INTEGER,
PRECIO_UNIDAD     INTEGER NOT NULL,
CANTIDAD          INTEGER NOT NULL,
DESCUENTO         INTEGER,
CONSTRAINT DETALLES_DE_PEDIDOS_FOREIGN_KEY FOREIGN KEY (ID_PEDIDO) REFERENCES PEDIDOS (ID_PEDIDO),
CONSTRAINT DETALLES_DE_PEDIDO_FOREIGN_KEY FOREIGN KEY (ID_PRODUCTO) REFERENCES PRODUCTOS (ID_PRODUCTO)
);


CREATE TABLE CATEGORIAS
(
ID_CATEGORIA        INTEGER PRIMAR KEY,
NOMBRE_CATEGORIA    VARCHAR2(100 CHAR) NOT NULL,
DESCRIPCION         VARCHAR2(100 CHAR) 
);



CREATE TABLE PRODUCTOS
(
ID_PRODUCTO            INTEGER PRIMARY KEY,
NOMBRE_PRODUCTO        VARCHAR2(50 CHAR) NOT NULL,
ID_PROVEEDOR           INTEGER,
ID_CATEGORIA           INTEGER,
CANTITAD_POR_UNIDAD    INTEGER,
PRECIO_UNIDAD          INTEGER,
UNIDADES_EN_EXISTENCIA INTEGER,
CONSTRAINT PRODUCTOS_FOREIGN_KEY FOREIGN_KEY (ID_PROVEEDOR) REFERENCES PROVEEDORES (ID_PROVEEDOR),
CONSTRAINT PRODUCTOS_FOREIGN_KEY FOREIGN_KEY (ID_CATEGORIA) REFERENCES CATEGORIAS (ID_CATEGORIA)
)

flyboy · #7 (**permalink**) 01-09-12, 10:18

Quote:

Well., I steel confuze, because in this thread http://www.dbforums.com/oracle/1672722-ora-02264-a.html one expert guy (i think that is a guru for the number of messages) give the next resolution

Maybe, if you read it carefully, you would find out, that the is not resolution, but another pointing to the duplicate constraint name (the same error you have in your code).

Well, you may take it from different angle.

Just imagine that you want to create two tables with the same name PEDIDOS. Does it make sense? How would you distinguish them if you wanted INSERT/DELETE/UPDATE rows from only one of them, or even, drop only one of them?

If you think that it is nonsense, just apply the same logic to constraints:

Just imagine that you want to create two constraints with the same name PEDIDOS_FOREIGN_KEY. Does it make sense? How would you distinguish them if you wanted disable, or even, drop only one of them?

pstnc · #8 (**permalink**) 01-09-12, 10:28

ahh., got it .. many thanks ., x10

shammat · #9 (**permalink**) 01-09-12, 10:29

Just replace the two lines:

CONSTRAINT PEDIDOS_FOREIGN_KEY FOREIGN KEY (ID_CLIENTE) REFERENCES CLIENTES(ID_CLIENTE),
CONSTRAINT PEDIDOS_FOREIGN_KEY FOREIGN KEY (ID_EMPLEADO) REFERENCES EMPLEADOS(ID_EMPLEADO))

with

CONSTRAINT PEDIDOS_CLIENTES_FK FOREIGN KEY (ID_CLIENTE) REFERENCES CLIENTES(ID_CLIENTE),
CONSTRAINT PEDIDOS_EMPLEADOS_FK FOREIGN KEY (ID_EMPLEADO) REFERENCES EMPLEADOS(ID_EMPLEADO))

Thus giving the two constraints two different names (Note that I abbreviated FOREIGN_KEY to FK because Oracle only allows 30 characters for any name)

pstnc · #10 (**permalink**) 01-09-12, 10:32

thank you shammat ., you guys are fast like F1!

shammat · #11 (**permalink**) 01-09-12, 10:34

Quote:

Originally Posted by pstnc

thank you shammat ., you guys are fast like F1!

I already told you that (albeit without an example) in my first answer...

pstnc · #12 (**permalink**) 01-09-12, 11:16

Quote:

Originally Posted by shammat

I already told you that (albeit without an example) in my first answer...

Yes but i don't realizate it., now i create 4 tables and everything is ok., but in the last one he give me a error - ORA-00902: invalid datatype

i attach a screenshot ., and i search to find this error and is this one

ORA-00902
Error: ORA-00902: invalid datatype

Causa: The datatype entered in the CREATE or ALTER TABLE statement is not valid.

Acción: Correct the syntax.

but what sintaxis!! i spent all day to create this little database that soon i am going be crazy!

this is the code

Code:

CREATE TABLE PROVEEDORES
(
ID_PROVEEDOR         INTEGER PRIMARY KEY,
NOMBRE_COMPANIA      VARCHAR2(50 CHAR) NOT NULL,
NOMBRE_CONTACTO      VARCHAR2(80 CHAR),
CARGO_CONTACTO       VARCHAR2(40 CHAR),
DIRECCION            VARCHAR2(100 CHAR),
CIUDAD               VARCHAR2(30 CHAR),
REGION               VARCHAR2(100 CHAR),
COD_POSTAL           INTEGER,
PAIS                 VARCHAR2(10 CHAR),
TELEFONO             INTEGER,
FAX                  INTEGER,
PAGINA_PRINCIPAL     VARCHAR2(30 CHAR),
CONSTRAINT PROVEEDORES_ENVIOS_FK FOREIGN_KEY (NOMBRE_COMPANIA) REFERENCES COMPANIAS_DE_ENVIOS (NOMBRE_COMPANIA),
CONSTRAINT PROVEEDORES_CLIENTES_FK FOREIGN_KEY (NOMBRE_CONTACTO) REFERENCES CLIENTES(NOMBRE_CONTACTO),
CONSTRAINT PROVEEDORES_CLIENT_FK FOREIGN_KEY (CARGO_CONTACTO) REFERENCES CLIENTES (CARGO_CONTACTO)
)

Littlefoot · #13 (**permalink**) 01-09-12, 12:31

It is rather simple; it is not "FOREIGN_KEY" but "FOREIGN KEY" (no underscore!) in a constraint syntax; should have been

Code:

...
CONSTRAINT PROVEEDORES_ENVIOS_FK FOREIGN KEY (NOMBRE_COMPANIA) REFERENCES COMPANIAS_DE_ENVIOS (NOMBRE_COMPANIA),
...

However, it would fail again because COMPANIAS_DE_ENVIOS table doesn't contain primary nor unique key on NOMBRE_COMPANIA column, so you should first create one, such as

Code:

alter table companias_de_envios add constraint uk_cde unique (nombre_compania);

Of course, you need to make sure whether it makes sense or not.

pstnc · #14 (**permalink**) 01-10-12, 04:00

Quote:

Originally Posted by Littlefoot

It is rather simple; it is not "FOREIGN_KEY" but "FOREIGN KEY" (no underscore!) in a constraint syntax; should have been

Code:

...
CONSTRAINT PROVEEDORES_ENVIOS_FK FOREIGN KEY (NOMBRE_COMPANIA) REFERENCES COMPANIAS_DE_ENVIOS (NOMBRE_COMPANIA),
...

Nice observation., but why in some cases is work like FOREIGN_KEY (with underscore) and in other cases like you told me (and is work!) is FOREIGN KEY (without underscore)!?

flyboy · #15 (**permalink**) 01-10-12, 04:20

Quote:

Originally Posted by pstnc

Nice observation., but why in some cases is work like FOREIGN_KEY (with underscore) and in other cases like you told me (and is work!) is FOREIGN KEY (without underscore)!?

Can you show those "some cases"? It never worked and never will with FOREIGN_KEY after its name (not as a part of the foreign key name) before column list. Only FOREIGN KEY (with space) is valid there.

For correct syntax, please, consult SQL Language Reference book for your Oracle version. It is available with other Oracle documentation books e.g. online on http://tahiti.oracle.com/

Internet Services

Web Development

Digital Marketing

Consumer Tech