Onde você deve definir chaves estrangeiras?

Coloque as chaves estrangeiras no banco de dados. Mesmo que você valide os dados no aplicativo antes de salvá-los, os FKs são uma boa peça de backup do QA. Para uma primeira aproximação, os aplicativos sempre têm problemas de dados. Deixar controles como esse fora do sistema apenas convida a modos de falha em que os dados são corrompidos silenciosamente.

Não há nada como trabalhar em armazenamento de dados por alguns anos para ver isso em ação. Você gasta seu tempo juntando as peças depois de erros cometidos por desenvolvedores de aplicativos que pensaram que poderiam impor a integridade dos dados no código do aplicativo. Passe algum tempo fazendo isso e você concluirá que a integridade dos dados gerenciados pelo aplicativo é pouco mais que uma presunção.

Além disso, o otimizador de consulta pode usar chaves estrangeiras para inferir coisas sobre junções de tabelas, portanto, os FKs resultarão em planos de consulta mais eficientes.

Existem muitos outros benefícios para chaves estrangeiras também. Faça um favor a todos - coloque os FKs no banco de dados.

A integridade referencial deve ser tratada no nível mais baixo possível, que seria o banco de dados subjacente. Os sistemas de gerenciamento de banco de dados relacional são otimizados para lidar com isso. Não faz sentido reinventar a roda proverbial.

É aceitável definir a lógica de domínio no código do aplicativo para evitar que a instrução DML cause uma exceção de RI, mas isso não deve ser visto como um substituto para relacionamentos de chave estrangeira no banco de dados.

Vou arriscar aqui, esperando que isso seja rejeitado, já que este é um grupo focado em DBA.

Concordo que usar chaves estrangeiras estritas é a melhor decisão na maioria dos cenários. No entanto, existem alguns casos em que as chaves estrangeiras causam mais problemas do que resolvem.

Quando você está lidando com um ambiente altamente simultâneo, como um aplicativo da Web de alto tráfego, e está usando um ORM robusto e bem estabelecido, as chaves estrangeiras podem causar problemas de bloqueio que dificultam o dimensionamento e a manutenção de um servidor. Ao atualizar linhas em uma tabela filho, a linha pai também é bloqueada. Em muitos cenários, isso pode limitar drasticamente a simultaneidade devido à contenção de bloqueio. Além disso, às vezes você precisa realizar manutenção em tabelas individuais, como processos de arquivamento em que pode ser necessário quebrar (intencionalmente) as regras de integridade referencial, pelo menos temporariamente. Com as chaves estrangeiras instaladas, isso pode ser incrivelmente difícil e, em alguns RDBMSes, desabilitar as restrições de chave estrangeira causará uma reconstrução da tabela, um processo demorado que pode exigir um tempo de inatividade substancial.

Entenda que estou incluindo a ressalva de que você deve usar uma estrutura robusta que seja capaz de entender a integridade referencial externa ao banco de dados. Ainda assim, você provavelmente acabará com alguns problemas de integridade referencial. No entanto, há muitos casos em que simplesmente não é grande coisa ter linhas órfãs ou pequenas violações de integridade referencial. Eu diria que a maioria dos aplicativos da web se enquadra nessa categoria.

Dito isto, ninguém começa como Facebook. Comece definindo chaves estrangeiras em seu banco de dados. Monitor. Se você acabar tendo problemas, entenda que pode ser necessário eliminar algumas dessas restrições de escala.

Em conclusão: a maioria dos bancos de dados deve ter chaves estrangeiras. Ambientes altamente simultâneos podem ficar melhores sem chaves estrangeiras. Se você chegar a esse ponto, talvez seja necessário considerar a eliminação dessas restrições.

Vou vestir meu traje retardador de chamas agora.

EDITAR 2012-03-23 ​​07:00

Ao pensar nas consequências de bloqueio das chaves estrangeiras, esqueci de mencionar o custo de todas as pesquisas de linha adicionais que são geradas implicitamente internamente, aumentando a carga do servidor.

Por fim, meu ponto é que as chaves estrangeiras não são gratuitas. Em muitos casos, o custo vale a pena, mas há cenários em que esse custo excede o benefício.

EDITAR 23/03/2012 07:38

Sejamos concretos. Estou escolhendo MySQL/InnoDB neste exemplo, que não é altamente respeitado por seu comportamento de chave estrangeira, mas é com o que estou mais familiarizado e é provavelmente o banco de dados da web mais usado. Não tenho certeza se outro banco de dados se sairia melhor com o exemplo que estou prestes a mostrar.

Considere uma tabela filho com uma chave estrangeira referenciando o pai. Como exemplo, veja as tabelas film e film_actor no banco de dados de exemplo sakila no MySQL:

CREATE TABLE `film` (
  `film_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `description` text,
  `release_year` year(4) DEFAULT NULL,
  `language_id` tinyint(3) unsigned NOT NULL,
  `original_language_id` tinyint(3) unsigned DEFAULT NULL,
  `rental_duration` tinyint(3) unsigned NOT NULL DEFAULT '3',
  `rental_rate` decimal(4,2) NOT NULL DEFAULT '4.99',
  `length` smallint(5) unsigned DEFAULT NULL,
  `replacement_cost` decimal(5,2) NOT NULL DEFAULT '19.99',
  `rating` enum('G','PG','PG-13','R','NC-17') DEFAULT 'G',
  `special_features` set('Trailers','Commentaries','Deleted Scenes','Behind the Scenes') DEFAULT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`film_id`),
  KEY `idx_title` (`title`),
  KEY `idx_fk_language_id` (`language_id`),
  KEY `idx_fk_original_language_id` (`original_language_id`),
  CONSTRAINT `fk_film_language` FOREIGN KEY (`language_id`) REFERENCES `language` (`language_id`) ON UPDATE CASCADE,
  CONSTRAINT `fk_film_language_original` FOREIGN KEY (`original_language_id`) REFERENCES `language` (`language_id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8

CREATE TABLE `film_actor` (
  `actor_id` smallint(5) unsigned NOT NULL,
  `film_id` smallint(5) unsigned NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`actor_id`,`film_id`),
  KEY `idx_fk_film_id` (`film_id`),
  CONSTRAINT `fk_film_actor_actor` FOREIGN KEY (`actor_id`) REFERENCES `actor` (`actor_id`) ON UPDATE CASCADE,
  CONSTRAINT `fk_film_actor_film` FOREIGN KEY (`film_id`) REFERENCES `film` (`film_id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8

A restrição relevante é film_actor (fk_film_actor_film) para o meu exemplo.

session1> BEGIN;
session1> INSERT INTO film_actor (actor_id, film_id) VALUES (156, 508);
Query OK, 1 row affected (0.00 sec)

session2> BEGIN;
session2> UPDATE film SET release_year = 2005 WHERE film_id = 508;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Observe que não consegui atualizar um campo não relacionado na linha pai ao inserir na tabela filho. Isso acontece porque o InnoDB está mantendo um bloqueio compartilhado na linha onde film.film_id = 508 devido à restrição FK em film_actor, portanto, o UPDATE para essa linha não pode obter o bloqueio exclusivo necessário. Se você inverter essa operação e executar o UPDATE primeiro, terá o mesmo comportamento, mas o INSERT será bloqueado.

session1> BEGIN;
session1> UPDATE film SET release_year = 2005 WHERE film_id = 508;
Query OK, 1 row affected (0.00 sec)

session2> BEGIN;
session2> INSERT INTO film_actor (actor_id, film_id) VALUES (156, 508);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Considere uma userstabela em um aplicativo da Web onde geralmente há dezenas de tabelas relacionadas. Basicamente, qualquer operação em qualquer linha relacionada impede uma atualização na linha pai. Isso pode ser um problema desafiador quando você tem vários relacionamentos de chave estrangeira e muita simultaneidade.

As restrições FK também podem tornar as soluções alternativas para a manutenção da tabela desafiadoras. Peter Zaitsev, da Percona, tem uma postagem no blog sobre isso que explica melhor do que eu: Hijacking Innodb Foreign Keys .

É uma boa prática usar chave estrangeira no banco de dados. Isso ajuda-

• para manter a integridade dos dados removendo a possibilidade de dados indesejados
• para aumentar o desempenho. Em sistemas que indexam automaticamente os campos, as referências de chave estrangeira podem aumentar o desempenho
• para escrever menos código pelo programador. como, usandoON DELETE CASCADE