There are some problems there, the ID or FK_POSTO are being referenced in the Insert and since they are not null fields, they must exist.
To start would create a quence to fill the id of table TB_POSTOS_NO_CONTRATO, as described below:
CREATE SEQUENCE SEQ_TB_POSTOS_NO_CONTRATO START WITH 1;
And then change the sql leaving it like this:
INSERT INTO TB_POSTOS_NO_CONTRATO (ID,FK_POSTO,DESCRICAO_POSTO)
SELECT SEQ_TB_POSTOS_NO_CONTRATO.NEXTVAL,
P.ID,
P.DESCRICAO
FROM TB_POSTO P, TB_POSTOS_NO_CONTRATO PC
WHERE PC.FK_POSTO = P.ID;
But this sql would include a possible insertion of the TB_POSTO ID that already existed in the table TB_POSTOS_NO_CONTRATO.
For example, if there was a row below in table TB_POSTOS_NO_CONTRATO:
ID | FK_POSTO | DESCRICAO_POSTO
1 | 1 | null
With this sql, a new line like the following would be created:
ID | FK_POSTO | DESCRICAO_POSTO
2 | 1 | "Descrição do posto"
Getting the table with the two lines:
ID | FK_POSTO | DESCRICAO_POSTO
1 | 1 | null
2 | 1 | "Descrição do posto"
To include the description in a row of a TB_POSTO ID that already existed in the TB_POSTOS_NO_CONTRATO table, you have to do an update and not an Insert, as we see below:
UPDATE TB_POSTOS_NO_CONTRATO
SET DESCRICAO_POSTO=(SELECT DESCRICAO FROM TB_POSTO WHERE TB_POSTOS_NO_CONTRATO.FK_POSTO =TB_POSTO.ID);
Using this sql would be updating the existing row, only including the description that would exist in the table TB_POSTO.
put the structure of the 2 tables
– Gabriel Oliveira
TB_POSTOS_NO_CONTRATO "ID" NUMBER NOT NULL ENABLE, "FK_POSTO" NUMBER NOT NULL ENABLE, "SALARIO_BASE" NUMBER(7,2), "DESCRICAO_POSTO" VARCHAR2(60), CONSTRAINT "TB_POSTOS_NO_CONTRATO_PK" PRIMARY KEY ("ID") USING INDEX ENABLE ALTER TABLE "TB_POSTOS_NO_CONTRATO" ADD CONSTRAINT "TB_POSTOS_NO_CONTRATO_FK" FOREIGN KEY ("FK_POSTO") REFERENCES "TB_POSTO" ("ID") ENABLE
– Jose Gustavo Lima
"TB_POSTO" ( "ID" NUMBER NOT NULL ENABLE, "DESCRICAO" VARCHAR2(150) NOT NULL ENABLE, CONSTRAINT "TB_POSTO_PK" PRIMARY KEY ("ID") USING INDEX ENABLE,
– Jose Gustavo Lima