Why does not the routine table from the employees table be transferred to the study table as an invalid?

Question

Why does not the routine table from the employees table be transferred to the study table as an invalid?

Navigation

#1 by (0 votes)

1

Why when I try to pass the foreign key which is a rut that is inside the table used for studies, it tells me that it is invalid? I attach images of the error and the tables.

SQL> CREATE TABLE EMPLEADOS(
  2  RUT CHAR(10),
  3  NOMBRE VARCHAR(10),
  4  APELLIDO1 VARCHAR(15),
  5  APELLIDO2 VARCHAR(15),
  6  DIRECC1 VARCHAR(25),
  7  DIRECC2 VARCHAR(20),
  8  CIUDAD VARCHAR(20),
  9  PROVINCIA VARCHAR(20),
 10  COD_POSTAL VARCHAR(5),
 11  SEXO VARCHAR(1) CHECK (SEXO IN ('H','M','h','m')),
 12  FECHA_NAC DATE);

Table created.

SQL> CREATE TABLE ESTUDIOS(
  2  UNIVERSIDAD NUMBER(5),
  3  ANO NUMBER,
  4  GRADO VARCHAR(3),
  5  ESPECIALIDAD VARCHAR(20));

Table created.

SQL> ALTER TABLE EMPLEADOS
  2  ADD CONSTRAINT pk_empleados PRIMARY KEY (RUT);

Table altered.

SQL> ALTER TABLE ESTUDIOS
  2  ADD CONSTRAINT pk_estudios PRIMARY KEY (UNIVERSIDAD);

Table altered.

SQL> ALTER TABLE ESTUDIOS
  2  ADD CONSTRAINT FK_EMPLEADOS_ESTUDIOS FOREIGN KEY (RUT)
  3  REFERENCES EMPLEADOS(RUT);
ADD CONSTRAINT FK_EMPLEADOS_ESTUDIOS FOREIGN KEY (RUT)
                                                  *
ERROR at line 2:
ORA-00904: "RUT": invalid identifier


SQL> ALTER TABLE ESTUDIOS
  2  ADD CONSTRAINT FK_ESTUDIOS_EMPLEADOS FOREIGN KEY (RUT)
  3  REFERENCES EMPLEADOS(RUT);
ADD CONSTRAINT FK_ESTUDIOS_EMPLEADOS FOREIGN KEY (RUT)
                                                  *
ERROR at line 2:
ORA-00904: "RUT": invalid identifier


SQL> EDIT
Wrote file afiedt.buf

  1  ALTER TABLE ESTUDIOS
  2  ADD CONSTRAINT FK_ESTUDIOS_EMPLEADOS FOREIGN KEY (RUT)
  3* REFERENCES EMPLEADOS(RUT))
SQL> /
REFERENCES EMPLEADOS(RUT))
                         *
ERROR at line 3:
ORA-01735: invalid ALTER TABLE option

sql oracle

asked by Sebastian Ortiz 18.11.2017 в 20:07

source

1 answer

I get an error when importing csv Share news on Facebook

score 0 · Answer 1

To create a relationship between 2 tables, you need to have the corresponding columns in both tables.

In your case, according to the following sentence:

ALTER TABLE ESTUDIOS
ADD CONSTRAINT FK_EMPLEADOS_ESTUDIOS FOREIGN KEY (RUT)
REFERENCES EMPLEADOS(RUT);

... you are trying to establish a relationship between ESTUDIOS and EMPLEADOS based on the column RUT .

But the problem is that that column does not exist in the ESTUDIOS table. You need to add it to that table first:

CREATE TABLE ESTUDIOS(
UNIVERSIDAD NUMBER(5),
ANO NUMBER,
GRADO VARCHAR(3),
ESPECIALIDAD VARCHAR(20),
RUT CHAR(10)); -- agrega la columna RUT

Then you can create the foreign key without problem.

As additional information, it is generally a good idea to define an index in the column to which a foreign key is defined, in this case ESTUDIOS.RUT .

I also notice that none of your columns are marked NOT NULL . It would be good to review that.