将“解除引用” SQL中的一个外键在理论上工作？ [英] Would "dereferencing" a foreign key in SQL theoretically work?

问题描述

假设我们有2个表，a和b：

pre code CREATE TABLE a（id_a INT NOT NULL PRIMARY KEY，id_b INT NOT NULL）
INDEX fk_id_b（id_b ASC），
CONSTRAINT fk_id_b FOREIGN KEY（id_b）
REFERENCES b（id_b）;
CREATE TABLE b（id_b INT NOT NULL PRIMARY KEY，b_data INT NOT NULL）;

以下列： id_a 和 id_b ，其中 id_b 是外键 b s id_b 。
当我想从a得到关联的b_data时，我必须做一个连接：

  SELECT id_a，b_data FROM a JOIN b ON a.id_b = b.id_b;  

它工作正常，但是很长，我重复一下我不应该根据红宝石家伙），所以我想办法使这个查询更短，更容易理解，仍然毫不含糊：

  SELECT id_a，id_b-> b_data FROM a;  

foreign_key->列会像一个指向结构的指针，数据库会自动加入所需的表。

我知道这是不存在的使它成为一个标准可能需要很多时间，我不会活在生产就绪数据库系统中看到它，有些人不希望它看起来很奇怪，但我至少想知道，如果它会是可能的，如果没有，为什么。

解决方案

关系的一个主要优点l数据模型是，它不需要依赖表之间硬编码的链接/指针/导航结构。数据访问是通过表和属性名称使用关系表达式，如连接。

在数据库中持久导航结构的模型将不太灵活和动态 - 当您更改表结构你会无效或不得不改变导航结构。你的问题也只涉及那些碰巧在外键上等同的连接。加入比这更一般。

Suppose we have 2 tables, a and b:

CREATE TABLE a (id_a INT NOT NULL PRIMARY KEY, id_b INT NOT NULL)
  INDEX fk_id_b (id_b ASC),
    CONSTRAINT fk_id_b FOREIGN KEY (id_b)
    REFERENCES b (id_b);
CREATE TABLE b (id_b INT NOT NULL PRIMARY KEY, b_data INT NOT NULL);

So a has the following columns: id_a and id_b, where id_b is a foreign key to bs id_b. When I want to get the associated b_data from a, I have to do a join:

SELECT id_a, b_data FROM a JOIN b ON a.id_b=b.id_b;

It works fine, but it's long, I repeat myself (which I shouldn't according to the ruby guys), so I thought of a way to make this query shorter, easier to understand and still unambiguous:

SELECT id_a, id_b->b_data FROM a;

foreign_key->column would behave like a pointer to a structure, the database would automatically join the needed tables.

I know this doesn't exist, that making it a standard would probably take so much time I wouldn't live to see it in production ready database systems and some people wouldn't want it as "it looks weird", but I would at least like to know, if it would be possible, and if not, why.

解决方案

One of the major advantages of the relational model of data is that it eliminates the need to rely on hard coded links/pointers/navigational structures between tables. Data access is via table and attribute names using relational expressions like joins.

A model that persisted navigational structures in the database would be less flexible and dynamic - when you changed table structures you would invalidate or have to change the navigational structures as well. Your question also only addresses those joins which happen to be equijoins on foreign keys. Joins are much more general than that.

这篇关于将“解除引用” SQL中的一个外键在理论上工作？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！