Self-relationship in mysql

Question

Self-relationship in mysql

Navigation

#1 by (1 votes)

1

I am trying to implement a table in MYSQL with self-relationship, it is a staff registry table, in which the administrator (FA) registers the common employee (FC) It would be registered in the tuple of the FC which FA registered it, I created the codfunc field 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 registers itself, if it is profile 2 it is a common employee and then it receives the code of the employee who registered it. for example:

CODFUNC profile name codfunc (F.K)

1      Jose   1(F.A)      Null

2      João   2(F.C)       1

I just can not do any insert the way I did. How could you create an auto relationship?

mysql chave-estrangeira

asked by anonymous 26.09.2016 / 01:51

1 answer

How to close uib-popover when clicking outside? Select the most repeated data in a column and display them

score 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've managed to hold the INSERT up normally, check it out and see if that's what you need. Just a question, will an employee's CODFUNC even have letters and numbers?