how do Insert a foreign key that is inside a foreign key?

Question

I intend to do Insert in the salary column and table function_name function which is foreign key in department table and this table is foreign key in official table...

But the two columns salary and payroll has to be done from the working table through the Id_deprtamento to get the Id_funcao that is inside the table

I did so

    CREATE TABLE FUNCIONARIO
(
    ID_FUNCIONARIO INT IDENTITY(1,1),
    NOME_FUNCIONARIO VARCHAR(100) NOT NULL,
    NOME_PAI VARCHAR(100) NOT NULL,
    NOME_MAE VARCHAR(100)NOT NULL,
    N_BI VARCHAR(50)UNIQUE NOT NULL,
    DATA_NASC VARCHAR(100) NOT NULL,
    GENERO VARCHAR(100) NOT NULL,
    ESTA_CIVIL VARCHAR(100)NOT NULL,
    FOTO VARCHAR (100) NOT NULL,
    ID_DEPARTAMENTO INT,
    ID_MUNICIPIO INT,
    DATA_HORA VARCHAR (100)NOT NULL,
    USUARIO VARCHAR (100)NOT NULL,
    CONSTRAINT PK_FUNC PRIMARY KEY (ID_FUNCIONARIO),

    CONSTRAINT PK_FUNC_MUNI FOREIGN KEY (ID_MUNICIPIO)
    REFERENCES FUNCAO (ID_FUNCAO),

    CONSTRAINT PK_FUNC_DEP FOREIGN KEY (ID_DEPARTAMENTO)
    REFERENCES DEPARTAMENTO (ID_DEPARTAMENTO)
)

CREATE TABLE DEPARTAMENTO
(
    ID_DEPARTAMENTO INT IDENTITY(1,1),
    NOME_DEP VARCHAR(100)NOT NULL,
    ID_FUNCAO INT ,
    CONSTRAINT PK_DEP PRIMARY KEY (ID_DEPARTAMENTO),
    CONSTRAINT PK_DEP_FUNCAO FOREIGN KEY (ID_FUNCAO)
    REFERENCES FUNCAO (ID_FUNCAO)
)
USE PRESENCA1
CREATE TABLE FUNCAO
(
    ID_FUNCAO INT IDENTITY(1,1),
    NOME_FUNCAO VARCHAR(100)NOT NULL,
    SALARIO VARCHAR(100)NOT NULL,
    DATA VARCHAR(100) NOT NULL,
    CONSTRAINT PK_FUNCAO PRIMARY KEY (ID_FUNCAO)
)

I’m using this device in Visual studio

INSERT INTO FUNCIONARIO (NOME_FUNCIONARIO,NOME_PAI,NOME_MAE,N_BI,DATA_NASC,GENERO,ESTA_CIVIL,FOTO,ID_DEPARTAMENTO ,ID_MUNICIPIO,DATA_HORA,USUARIO

The field Function name and Salary of the table Insert from the table official, but these two tables are foreign key in the table department and the table is foreign key in the table STAFF

These two fields have to be shown in the official table

Answer 1

I’m new around here, I can’t comment because I don’t have points, so I’m going to respond as best I can.

See that in table creation FUNCIONARIO is informing the restriction that the ID_MUNICIPIO reference ID_FUNCAO

CONSTRAINT PK_FUNC_MUNI FOREIGN KEY (ID_MUNICIPIO) REFERENCES FUNCAO (ID_FUNCAO)

This is probably a mistake, because in case you are passing one ID_MUNICIPIO, for example ID_MUNICIPIO = 2125 and if there is no such value in ID_FUNCAO table Funcao, will lead to a primary key breach error.

Check the values that are being reported in your insert and then check if foreign key values exist in the tables being referenced in the table creation commands REFERENCES.

If you really want to have the ID_Funcao on the table Funcionario, include this new field, but don’t try to reuse a field like ID_MUNICIPIO that is already populated with information that referred to another table.

At your command insert is missing the word VALUES, correct:

INSERT INTO FUNCIONARIO VALUES ('Geek_Aprendiz','Alan Turing','Ada Lovelace','BI 123','01/01/2001','MASCULINO','SOLTEIRO','FOTO01.JPG',1,2125,'15/04/2021 11:21:32','USUÁRIO 123')

In this example above, if there is no function with Id_funcao = 2125, error will occur:

The INSERT statement conflicted with the FOREIGN KEY constraint "PK_FUNC_MUNI". 
The conflict occurred in database "PRESENCA1", table "dbo.FUNCAO", column 'ID_FUNCAO'.

Considering an empty database, the following commands were successful:

insert into FUNCAO VALUES('PROGRAMADOR','7.000,00','10/01/2021')
insert into FUNCAO VALUES('DESENVOLVIMENTO DE SOFTWARE','10.000,00','01/01/2000')
insert into DEPARTAMENTO VALUES ('GERÊNCIA DE SOFTWARE WEB',2);
INSERT INTO FUNCIONARIO VALUES ('Geek_Aprendiz','Alan Turing','Ada Lovelace','BI 123','01/01/2001','MASCULINO','SOLTEIRO','FOTO01.JPG',1,2,'15/04/2021 11:21:32','USUÁRIO 123')

To solve your problems:

1. Correct, if necessary, CONSTRAINT PK_FUNC_MUNI FOREIGN KEY (ID_MUNICIPIO) REFERENCES FUNCAO (ID_FUNCAO)
2. Please correct your command insert, is missing the word VALUES;
3. Check the data being sent on your insert

Please let us know if the suggested explanation and solution solved your problem, I am an apprentice seeking to learn every day.