Auto-relationship in mysql

Asked

Viewed 1,944 times

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?

mysql foreign-key
  • 1

    are you using Pdo, mysqli ? as you are making the code!

  • I’m just implementing the bank for now on Workbench

  • 1

    @Mrbobot ,put the code of your CREATE TABLE and INSERTS, so we can see what can be improved.

  • How are you doing the insert?

  • @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), INDEX codfunc_idx (codfunc1 ASC), CONSTRAINT codfunc FOREIGN KEY (codfunc1) REFERENCES mydb.FUNC (CODFUNC) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = Innodb DEFAULT CHARACTER SET = utf8;

  • @Virgilionovic, I tried to insert F.A : (1, Joseph ,1 , null) but it didn’t work

  • 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!

Show 2 more comments

1 answer

1

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), 
    INDEX codfunc_idx (codfunc1 ASC), 
    CONSTRAINT codfunc 
        FOREIGN KEY (codfunc1) 
        REFERENCES mydb.FUNC (CODFUNC) 
        ON DELETE NO ACTION 
        ON UPDATE NO ACTION) 
    ENGINE = InnoDB DEFAULT 
    CHARACTER SET = utf8;

    INSERT INTO FUNC(CODFUNC,NOME,PERFIL,CPF,CODFUNC1) VALUES ("1","BATMAN",1,1234567899,"1");

    INSERT INTO FUNC(CODFUNC,NOME,PERFIL,CPF,CODFUNC1) VALUES ("2","LANTERNA VERDE",2,0987654321,"1");

    INSERT INTO FUNC(CODFUNC,NOME,PERFIL,CPF,CODFUNC1) VALUES ("3","AQUAMAN",2,343423433,"2");

    SELECT * FROM FUNC;

I was able to perform the INSERT up normally, check and see if it’s what you need. Just one question, an employee’s CODFUNC will actually have letters and numbers?.

  • worked out the way you did, thanks. I was doing the direct Insert without specifying the fields and was not accepting.

Browser other questions tagged mysql foreign-key

You are not signed in. Login or sign up in order to post.