I'm looking for a DB server that ensure the intetrity constrain over the DB. (PK/FK)

Is MySQL such a DB ?

H2O

For integrity constraints I guess Oracle is really powerful.
Pooja

Foreign Keys are not supported in MySQL

MySql can support Foreign Keys and other constraint with innodb table type

See http://www.lasso-developpeur.net/us/...tab=db&lang=us

Yes, you're right . The syntax is at:
http://www.mysql.com/doc/en/InnoDB_f...nstraints.html

It seems to be a bit complicated:
(at least it is not an SQL standard to have an index explicitaly defined to obtain the referential integrity.)

------------------------------
The syntax of a foreign key constraint definition in InnoDB:

[CONSTRAINT symbol] FOREIGN KEY (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.
---------------------------------

The other table type to support referential integrity seems to be BDB (Berkley DB), and it seems also not to have such a strange definitions to achieve the goal.
The more strange is that, with innoDB the mysql is far more quick, but the standard tables are not the innoDB, but the MyIsam ones....

And again to get the InnoDb tables you have to use a non ANSI SQL (not portable) predicate into the CREATE statement (Table=InnodB).


Disappointing....


H2O

If you want to make your code portable, you could write a create table statement with the type wrapped in a comment like this:

CREATE TABLE test ( <column definitions> ) /*!Type=InnoDB*/;

MySQL will parse a comment started with "/*!" as part of the issued statement, but other SQL servers will ignore it.

Why do you think that InnoDB tables are faster than MyISAM? MyISAM does not have the same overhead that InnoDB does. MySQL AB recommends using MyISAM and other non-transaction-safe tables for speed.

http://www.innodb.com/bench.html
InnoDB and MyISAM table types in MySQL
I ran the tests on a Linux 2-CPU Xeon 450 MHz. The times below are wall clock times. The results were:

InnoDB MyISAM
------------------------------------------------------------
100 000 inserts 25 s. 40 s.

100 000 selects on
primary key 57 s. 58 s.

100 000 selects on
secondary key 68 s. 95 s.
------------------------------------------------------------


Consider also the benefit that could come also from all theese features:
http://www.innodb.com/howtouse.html

H2O
P.S:
By the way: the perf. bechmark publisced by e-week was of course with MySql+InnoDB.......

I have run some benchmarks of my own that do not look anything like that. Using a simple table structure and a simple loop in Python to insert 10 000 rows, the InnoDB table type is at least 5 times slower. I got results of a little over 7 sec for MyISAM and a little more than 39 sec for InnoDB on my PIII 500. Take a look at the python code that I have included. Also, in the eWeek article, the team chose InnoDB only when the spec required transaction-safe tables. They chose MyISAM tables everywhere else.

ahhh interesting ....
the power of InnodDB marketing .... quite disappointing...
May be the difference stay with the use of indexes.... ?

quoting from ... http://www.innodb.com/bench.html
"
...
I wrote a Perl program which inserts 100 000 rows to a table with 3 integer columns and two indexes. Then another Perl program fetches each row either through a secondary index or the primary key.
The Perl programs for each test are at the end of this web page.
".

In peformace analisis, indexes playe a BIG role...
I'm sorry not having php installed (i'm just a beginner with Mysql...) but
I suggest a db example, to find if the peformance in and indexed Innodb database overcome the MyIsam perfomance:

Database:
a three fields database (
ID-counter-pk,
ind- integer- indexed
data- varchar - )

We (you ) can Create the db, setting the ind field to the recnum.
Then, acces them (better randomly, but I think is the same as you acces them by an indexed field...) and update the data value.

May the perfomance will change ?



Can you make me a favor ?
If you got the MS MSDE engine (the "open" SQL server, Desktop edition) can you run the same test with it, just to have an idea of which are the perf. differences between the two referential-constrain solution ....(InnoDB vs MSDE).

I would very apprechiate ....

H2O

I ran the test like you specified and got some interesting results. The two table types performed neck and neck. Sometimes InnoDB was in front and other times MyISAM was faster. The difference was almost never more than one-tenth of a second. The record selected was random, but in the same order for the two tables. I did not use the recnum for ind, but rather 10000 - recnum. When I changed the table definition from varchar(24) to char(24), MyISAM came out ahead of InnoDB by two-tenths of a second every time. This shows that a variable length column affects MyISAM more than InnoDB. It goes along with what MySQL AB has to say about performance, since they recommend fixed-length columns whenever possible.

I can't test SQL Server at the moment, but maybe I can later in the week.