1
I am trying to implement a table in MYSQL with self relationship, it is a table of employee registration, in which the administrator employee (F.A) registers the common employee (F.C) It would be registered in the tuple of F.C which F.A registered it, I created the field codfunc as the primary key and foreign key in this table to create the auto relationship, if the profile is 1 it is FA, so it auto registers, if it is profile 2 is Common Employee and then it gets the code from the employee who registered it. for ex:
CODFUNC name codfunc profile(F.K)
1 Jose 1(F.A) Null
2 João 2(F.C) 1
I just can’t do any Insert the way I did. How could I create a self relationship?
are you using Pdo, mysqli ? as you are making the code!
– novic
I’m just implementing the bank for now on Workbench
– Mr bobot
@Mrbobot ,put the code of your CREATE TABLE and INSERTS, so we can see what can be improved.
– Marcus Vinicius
How are you doing the
insert
?– novic
@Marcusvinicius CREATE TABLE IF NOT EXISTS
mydb
.FUNC
(CODFUNC
VARCHAR(5) NOT NULL,NOME
VARCHAR(45) NULL DEFAULT NULL,PERFIL
TINYINT(4) NOT NULL,CPF
BIGINT(16) NOT NULL,codfunc1
VARCHAR(45) NOT NULL, PRIMARY KEY (CODFUNC
), INDEXcodfunc_idx
(codfunc1
ASC), CONSTRAINTcodfunc
FOREIGN KEY (codfunc1
) REFERENCESmydb
.FUNC
(CODFUNC
) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = Innodb DEFAULT CHARACTER SET = utf8;– Mr bobot
@Virgilionovic, I tried to insert F.A : (1, Joseph ,1 , null) but it didn’t work
– Mr bobot
So, you are doing the wrong table, the codfunc should be int and its respective too, profile does not need already seen that if the respective code is null is the father of all. Well I’d do it totally different than what’s there!
– novic