How to relate two tables without a foreign key?

Question

Can someone give a demo?

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

EDIT

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

Answer 1

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).

See: 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.

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:

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

Answer 2

... deleted ...

Answer 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.

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

Answer 4

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.

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.

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

Answer 5

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.