foreign key su mysql

[dona]

ciao , dovrei lavorare sul sorgente di mysql per provare a creare il supporto dei vincoli foreign key, per una futura tesi.

qualcuno sa darmi qualche dritta?
qualcuno sa dove posso reperire guide o chissa che sui sorgenti di mysql?

commenti o consigli sono di aiuto

[cionci]

Dal manuale di MySQL :

1.4.4.5 Foreign Keys

Note that foreign keys in SQL are not used to join tables, but are used mostly for checking referential integrity (foreign key constraints). If you want to get results from multiple tables from a SELECT statement, you do this by joining tables:

SELECT * from table1,table2 where table1.id = table2.id;

See section 6.4.1.1 JOIN Syntax. See section 3.5.6 Using Foreign Keys.

The FOREIGN KEY syntax in MySQL exists only for compatibility with other SQL vendors' CREATE TABLE commands; it doesn't do anything. The FOREIGN KEY syntax without ON DELETE ... is mostly used for documentation purposes. Some ODBC applications may use this to produce automatic WHERE clauses, but this is usually easy to override. FOREIGN KEY is sometimes used as a constraint check, but this check is unnecessary in practice if rows are inserted into the tables in the right order. MySQL only supports these clauses because some applications require them to exist (regardless of whether or not they work).

In MySQL, you can work around the problem of ON DELETE ... not being implemented by adding the appropriate DELETE statement to an application when you delete records from a table that has a foreign key. In practice this is as quick (in some cases quicker) and much more portable than using foreign keys.

In the near future we will extend the FOREIGN KEY implementation so that at least the information will be saved in the table specification file and may be retrieved by mysqldump and ODBC. At a later stage we will implement the foreign key constraints for application that can't easily be coded to avoid them.

1.4.4.6 Why We Did Not Implement Foreign Keys

Many database scholars and programmers feel very strongly that referential integrity should be enforced inside the database server. Indeed, in many cases, this approach is very helpful. However, in talking with many database users we have observed that foreign keys are often misused, which can cause severe problems. Even when used properly, it is not a magic solution for the referential integrity problem, although it does make things easier in some cases.

Because of the above observations, we did not assign implementing foreign keys a high priority. Our user base consisted of mostly of developers who did not mind enforcing referential integerity inside the application code, and in fact, preferred to do it that way because it gave them more control.

However, in the last couple of years, our user base has expanded a great deal and we now have many users who would like to have the enforced referential integrity support inside MySQL. So we will implement the foreign keys in the near future, although at this point we cannot provide a definite delivery date.

Some advantages of foreign key enforcement:

Assuming proper design of the relations, foreign key constraints will make it more difficult for a programmer to introduce an inconsistency into the database
Using cascading updates and deletes can simplify the client code
Properly designed foreign key rules aid in documenting relations between tables
Disadvantages:

MySQL does not yet support enforced referential integrity, so if your application depends on it, you will not be able to use it with MySQL until we implement this feature.
Mistakes, that are easy to make in designing key relations, can cause severe problems, for example, circular rules, or the wrong combination of cascading deletes.
A properly written application will make sure internally that it is not violating referential integrity constraints before proceding with a query. Thus, additionaly checks on the database level will only slow down performance for such application.
It is not uncommon for a DBA to make such a complex topology of relations that it becomes very difficult, and in some cases impossible to backup or restore individual tables.

Questa è la roadmap di MySQL...sembra che il supporto alle foreign keys sia già in fase di sviluppo...visto che siamo già alla versione alpha della 4.1...

Note that because we know the MySQL road map, we have included in the following table the version when MySQL should support this feature. Unfortunately we couldn't do this for previous comparison, because we don't know the PostgreSQL roadmap.

Feature MySQL version
Subselects 4.1
Foreign keys 4.0 and 4.1
Views 4.2
Stored procedures 4.1
Extensible type system Not planned
Unions 4.0
Full join 4.0 or 4.1
Triggers 4.1
Constrainst 4.1
Cursors 4.1 or 4.2
Extensible index types like R-trees R-trees are planned for 4.2
Inherited tables Not planned

[cionci]

Non vorrei darti una brutta notizia, ma MySQL supporta le Foreign Keys su database di tipo InnoDB...
Le tabelle InnoDB sono diverse da quelle di default (MyISAM)...ed usano le transazioni per mantenere le proprietà ACID...

http://www.mysql.com/doc/en/SEC450.html