Início / dba / Perguntas / 29170
Accepted
Grijesh Chauhan
Asked: 2012-11-24 04:01:08 +0800 CST

Restrição à auto-referência na inserção no MYSQL?

  • 772

Como restringir a inserção na adição de linhas de auto-referência em uma tabela de relação recursiva (uma tabela contém pontos de chave estrangeira em si)? 

mysql> SELECT * FROM Employee;
+-----+------+-------------+------+
| SSN | name | designation | MSSN |
+-----+------+-------------+------+
| 1   | A    | OWNER       | NULL |
| 2   | B    | BOSS        | 1    |
| 3   | C    | WORKER      | 2    |
| 4   | D    | BOSS        | 2    |
| 5   | E    | WORKER      | 4    |
| 6   | F    | WORKER      | 1    |
| 7   | G    | WORKER      | 4    |
| 8   | H    | BOSS        | 8    |
+-----+------+-------------+------+
8 rows in set (0.00 sec)

Um empregado não pode ser o BOSSde si mesmos. Por isso 

mysql> INSERT INTO Employee VALUES ("8", "H", "BOSS",   "8");  
Query OK, 1 row affected (0.04 sec)

deve ser recusado por alguma restrição. Como alguém pode adicionar tal restrição?

Se possível sugira sem usar um gatilho.

mysql mysql-5.5

1 respostas

  1. Best Answer

    Isso seria facilmente resolvido com uma restrição de verificação, mas ainda não foi implementada no MySQL (elas são apenas analisadas por "razões de compatibilidade" durante a CREATE TABLEdefinição e completamente ignoradas depois disso).

    Portanto, a solução 1 é bastante óbvia. Mude para um DBMS que tenha CHECKrestrições implementadas, como SQL-Server, Oracle, Postgres, etc. (até o MS-Access tem!):

    ALTER TABLE Employee
  ADD CONSTRAINT Employee_cannot_be_Boss_of_himself
    CHECK (mssn <> ssn) ;

    A solução 2 é descartar a designationcoluna completamente. Da sua descrição, todos 'Owners'têm null mssne todos os outros têm seus "gerentes" ssnarmazenados na mssncoluna.

    Depois de descartar essa coluna, você sempre pode calculá-la em uma exibição:

    CREATE VIEW EmployeeDesignated AS
  SELECT ssn
       , name
       , CASE WHEN (mssn IS NULL OR mssn = ssn) 
                THEN 'OWNER'
              WHEN EXISTS (SELECT * FROM Employee ew WHERE ew.mssn = e.ssn)  
                THEN 'BOSS'
              ELSE 'WORKER'
         END AS designation
       , mssn
  FROM Employee AS e ;

    Isso apenas reforça que, quando um novo funcionário for inserido, se o mssnfor igual ao ssn, o funcionário será atribuído como 'Proprietário'.

    Porém, você pode ter problemas de desempenho, dependendo de como usa a designationcoluna. Visualizações dentro de visualizações ou consultas complexas não são o melhor negócio para o otimizador do MySQL.

    A solução 3 é emular a CHECKrestrição usando o método descrito nesta resposta: Verificar restrição não funciona?

    Isso requer que você atualize para o MySQL versão 5.7 ou para o MariaDB 5.5.

    A solução 4 é esperar que eles sejam implementados. Há rumores (é 2016 agora) que eles estarão na próxima versão.

    • 2

relate perguntas