How is MySQL relational?

osheriff

If MySql does not implement foriegn keys, how is it relational. What I'm saying may be made clearer in the following example:

I have a student_id in two seperate tables, one is the primary key the other is the foriegn key. If I change the primary key (students id number) I expect the foriegn to reflect this change. I don\'t want to have to update both tables.

Can someone tell me how to do this in MySQL, if not what is the closest thing to it in MySQL.

osheriff

sorry for double posting, my machine didn't show the post

[deleted]

MySQL does support foreign keys when yiou use InnoDB tables instead of MyISAM tables.

Without InnoDB, MySQL does cannot enforce any kind of relations. That does not mean the database is not relational, the relations are just not enforced by the database, allowing inconsistent data.

PS: one does not change primary-key values.

If you want full relation support, forget MySQL and use PostgreSQL, that also supports cascading etc.

lucianoes

Cool, Vincent. Now, do you know of any spot in the Web that will let me download a pre-compiled, ready-to-go Windows version of PostgreSQL?

Cheers and props,
Luciano ES
Santos, SP - Brasil

osheriff

thanks for the reply vincent. When you say primary keys cannot be changed can you explain. I will tell you what I'm trying to do and you can tell me where I'm going wrong.

For instance a student's username (which is the primary key) is incorrect I now want to correct this, does that mean I cannot change the primary key. Please explain how I can implement a way of doing the above.

Thanks in advance
Sheriff

amcgrath

you could change it, but make sure you change it something that isnt already in use.... why not use an autoincrement value as your pk though? just curios....

Anon

I agree. I use autoincrement integers for my primary keys and it works great. I wouldn't do it any other way.

My 2 cents,
Jerod

osheriff

i wanted to be able to use username in another table as the foreign key hence my use of username as primary key in my first table.

How do I keep my data consistent if all tables referencing my primary key need to have their username info changed as well.

[deleted]

Postgres, like any other database except MS-SQL works best on un*x systems.

For windows, look for 'cygwin'. But my advice would be to setup a small linux box.

[deleted]

Your idea is good, but your implementation is not :-)

A username is not a very good choice for a primary key, because it can change, and like you said, if it changes it much change in all references aswell, or the data explodes.

Therefor, data such as name, address, phone etc should be stored in only one table, where each record has a primary key consisting of an auto_incremented number.

All other tables that reference this userdata would have a foreign key pointing to that auto_incrementing column of the userdata's primary key.

Now you can change the names as often as you like, that does not change the value of the primary key, which means your data stays in tact without any updates.

Another reason not to use a name for a primary key is that it is perfectly ok to have two persons with the same name. it's very possible to have two John Smith's, but a primary key cannot contain duplicates.

lucianoes

What if I want to build a glossary and need a primary key made of words, so that everything is kept in alphabetical order? I am having some trouble handling that right now.

Luciano ES
Santos, SP - Brasil

Anon

You don't have to use the primary key to keep it alphabetical. Use a autoincrement number for your primary key and in your SQL query, do ORDER BY glossaryword asc (where glossaryword is the name of the field that you want to list in alphabetical order)

lucianoes

Oh, sure, "order by"... thanks a lot.

One would think I am supposed to know that already, but I just installed mySQL and started learning how to use it. Don't worry, I am already R'ing TFM like crazy, I won't bother you with lame questions in a long time.

Best regards,
Luciano ES
Santos, SP - Brasil