如何在没有外键的情况下连接两个表?

Can someone give a demo?

有人能做个演示吗?

I'm using MySQL,but the idea should be the same!

我用的是MySQL，但是想法应该是一样的!

EDIT

编辑

In fact I'm asking what's the difference between Doctrine_Relation and Doctrine_Relation_ForeignKey in doctrine?

事实上，我想问的是教义上的关系和教义上的关系有什么不同?

4 个解决方案

#1

I suspect what you are looking at would be to be map columns from one db table to another db table. You can do this using some string comparison algorithm. An algo like Levenstein or Jaro-Winkler distance would let you infer the "matching" columns.

我猜想您要查看的是将列从一个db表映射到另一个db表。你可以使用一些字符串比较算法。像Levenstein或Jaro-Winkler这样的算法可以让你推断出“匹配”列。

For example, if db1.tableA has a column L_Name and db2.tableB has a column LastName, a string distance match would fetch you one measure. You can extend that by comparing the values in the rows to check if there is some consistency for example if the values in both tables contains: "Smith"s, "Johnson"s etc. you have a double-win.

例如,如果db1。表a有一个列L_Name和db2。表b有列LastName，字符串距离匹配将为您带来一个度量。您可以通过比较行中的值来扩展它，以检查是否存在某种一致性，例如，如果两个表中的值都包含:“Smith”s、“Johnson”s等等，那么您将获得双赢。

I recently did something similar, integrating multiple large databases (one of them in a different language - French!) and it turned out to be quite a great experience.

我最近做了一件类似的事情，整合了多个大型数据库(其中一个用了不同的语言——法语!)

HTH

#2

You should use foreign keys to relate tables in MySQL, because it does not offer other ways to create relationships (such as references or nested tables in an object-oriented database).

您应该使用外键来关联MySQL中的表，因为它不提供创建关系的其他方法(比如在面向对象的数据库中创建引用或嵌套表)。

See: http://lists.mysql.com/mysql/206589

参见:http://lists.mysql.com/mysql/206589

EDIT: If you are willing to use Oracle, references and nested-tables are alternate ways to create relationships between tables. References are more versatile, so here is an example.

编辑:如果您愿意使用Oracle，引用和嵌套表是在表之间创建关系的替代方法。引用更加通用，这里有一个例子。

Since references are used in object-oriented fashion, you should first create a type and a table to hold objects of that type.

由于引用是以面向对象的方式使用的，您应该首先创建一个类型和一个表来保存该类型的对象。

Lets create an object type of employee which has a reference to his manager:

让我们创建一个对象类型的employee，它有一个对他的经理的引用:

CREATE TYPE employee_type AS OBJECT (
      name     VARCHAR2(30),
      manager  REF manager_type 
);

We should also create an object type for managers:

我们还应该为管理者创建一个对象类型:

CREATE TYPE manager_type AS OBJECT (
      name     VARCHAR2(30),
);

Now lets create two tables, one for employees and other for managers:

现在让我们创建两个表，一个为员工，另一个为经理:

CREATE TABLE employees OF employee_type;
CREATE TABLE managers OF manager_type;

We can relate this tables using references. To insert an employee in employees table, just do this:

我们可以使用引用来关联这个表。要在雇员表中插入雇员，只需这样做:

INSERT INTO employees 
  SELECT employee_type('Bob Jones', REF(m))
    FROM managers m
    WHERE m.name = 'Larry Ellison';

More info: Introduction to Oracle Objects

更多信息:Oracle对象介绍

#3

Well you could get around that by taking care of relationships in a server side language. Some database abstraction layers can handle this for you (such as Zend_Db_Table for PHP) but it is recommended to use foreign keys.

您可以通过使用服务器端语言来处理关系来解决这个问题。一些数据库抽象层可以为您处理这个问题(比如PHP的Zend_Db_Table)，但是建议使用外键。

MySQL has InnoDB storage engine that supports foreign keys and also transactions.

MySQL有InnoDB存储引擎，支持外键和事务。

#4

Using a foreign key is the standard way of creating a relationship. Alternatives are pretty much nonexistent, as you'd have to identify the related rows SOMEHOW.

使用外键是创建关系的标准方式。替代几乎是不存在的，因为您必须以某种方式识别相关的行。

A column (or set of columns) which links the two tables IS a foreign key - even if it doesn't have a constraint defined on it (or even an index) and isn't either of the tables' primary key (although in that case you can end up with a weird situation where you can get unintended cartesian products when joining, as you will end up with a set vs set relationship which is probably not what you want)

(一列或一组列)连接两个表的外键,即使它没有约束定义在它(甚至一个索引),不是表的主键(尽管在这种情况下,你可以得到一个奇怪的情况你可以得到意想不到的笛卡尔产品加入时,您将得到一组与组之间的关系可能不是你想要的)

Not having a foreign key constraint is no barrier to using a foreign key.

没有外键约束对于使用外键没有障碍。

#1