PostgreSQL, MySQL, general DB questions

[dustbuster]

I have been using MySQL for a while now but have been wondering about using PostgreSQL. I hope someone can answer these questions or point me in the right direction.

Is there a place where I can view a comparrison of MySQL vs PostgreSQL? Or can someone give me a good overview?

I've heard that PostgreSQL supports referential integrity. Is this true? Does MySQL support this as well. Foreign keys in MySQL are a bit of a mystery to me.

Let's say I have 2 tables:
Table1
- id (primary key)
- column1

Table2
- id (primary key)
- table1_id (foreign key from Table1)
- column1

I want to delete a record from table 1. Will either PostgreSQL or MySQL simply delete the record from Table1 and its corresponding entries for Table2? Or will I have to first delete the entries from Table2 then once their gone delete the entries from Table1.

I want to be able to put multiple languages (English,Chinese,Spanish...) into fields in my database. How would I do this and how hard is it do?

I will probably have more questions but if someone can show me a really good resource for this that would be great.

[Sxooter]

Yep, postgresql supports that and more. It's called a cascading foreign key. You defined the dependent table with an on delete cascade option and it will do it.

It's hard to find a feature comparison, since most of the higher end features Postgresql has aren't in MySQL.

For a list of features that do and don't conform to the SQL 92 standard, look here:

http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=features.html

Note that the documentation for Postgresql generally assumes a higher level of knowledge about database design and theory than that of MySQL. I.e. it doesn't hold your hand as well. If you already know databases pretty well, this is no big deal, if you're learning, it can be a pretty big stepping stone.

I'd recommend getting familiar with the user docs and the administrator docs to begin with.

For a list of all the SQL commands, look here:

http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=reference.html

For a slightly dated comparison (it compares pgsql 7.0.3, pgsql is now up to 7.3.3 with 7.4 due out in beta in a month or less) read the article here on PHPBuilder from Tim Perdue about his comparison of the two under heavy load:
http://www.phpbuilder.com/columns/tim20000705.php3

[Sxooter]

Oh, and here's the later article by Tim, comparing pgsql 7.1 to mysql:

http://www.phpbuilder.com/columns/tim20001112.php3