Re: Is mysql a RDBMS ?

Morten,

"Morten Gulbrandsen" <Morten.Gulbrandsen_at_rwth-aachen.de> kirjoitti viestissä news:60ca69db.0308210016.822e230_at_posting.google.com...
> Hello,
>
> I have only read Elmasri/Navathe and I find it nearly impossible
> to implement any non-trivial Enhanced Entity relationship diagram
> in MySQL,
> according to the mailing list, important foreign key constraints are
> not supported,

foreign key constraints are supported in MySQL. Where did you find on the mailing list the claim that they are not? I do not remember seeing that claim for a long time. InnoDB does not support ON ... SET DEFAULT, but that is not really a constraint, but an 'action', similar to a trigger.

http://www.innodb.com/ibman.html#InnoDB_foreign_keys "
The syntax of a foreign key constraint definition in InnoDB: [CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...)

                  REFERENCES table_name (index_col_name, ...)
                  [ON DELETE {CASCADE | SET NULL | NO ACTION
                              | RESTRICT}]
                  [ON UPDATE {CASCADE | SET NULL | NO ACTION
                              | RESTRICT}]

Both tables have to be InnoDB type, in the table there must be an INDEX where the foreign key columns are listed as the FIRST columns in the same order, and in the referenced table there must be an INDEX where the referenced columns are listed as the FIRST columns in the same order. InnoDB does not auto-create indexes on foreign keys or referenced keys: you have to create them explicitly. The indexes are needed for foreign key checks to be fast and not require a table scan.
...
An example:
CREATE TABLE parent(id INT NOT NULL,

                      PRIMARY KEY (id)) TYPE=INNODB;
CREATE TABLE child(id INT, parent_id INT,
                      INDEX par_ind (parent_id),
                      FOREIGN KEY (parent_id)
                        REFERENCES parent(id)
                        ON DELETE CASCADE) TYPE=INNODB;

> ===
> "Morten Gulbrandsen" <mgu_at_owi-aachen.de> wrote:
...
> It is the most popular SQL server,
> so I can start to believe that alone MySQL is no RDBMS,
> unless it is combined with another programming language,
>
> Is this true ?
>
> From Elmasri:
>
> To qualify as a genuine relational DBMS,
> a system must have at least the following properties:
>
> 1.) It must store data as relations such that each column is
> independently identified by its column name and the ordering of rows
> is immaterial.

This holds for MySQL.

> 2.) The operations available to the user, as well as those used
> internally by the system, should be true relational operations; that
> is, they should be able to generate new relations from old relations.

This holds for MySQL.

> 3.) The system must support at least one variant of the JOIN
> operation.

This holds for MySQL.

Thus, according to the definition of Elmasri and Navathe, MySQL/InnoDB is a relational database. It is not a relational database according to so-called Codd's 12 rules, but none of the well-known databases satisfies all those rules.

> Yours Sincerely
>
> Morten Gulbrandsen

Best regards,

Heikki Tuuri
Innobase Oy
http://www.innodb.com
Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a non-free hot backup tool for MySQL Download MySQL-4.0 from http://www.mysql.com Received on Sun Aug 24 2003 - 13:21:32 CEST