Primary Key vs. IND. + Unique + Not null

[Anon]

Hi, is there any need for a Primary Key, whem I have an unique Index with 'not null' on a field or does mysql handle both configurations different?

thx

[[deleted]]

A PK forces a UNIQUE contraint, an index and a NOT NULL.

In MySQL you could probably do without a PK and just use the UNIQUE, index and NOT NULL, but then again, MySQL is not a real RDBMS.

Real RDBMS's use the PK for forgein-key contraints etc.

[Anon]

Aaaaa, thank you very much, that was the stone that was missing in my mosaik 🙂

I've never worked with foreignn keys and never really needed them. Some people say they are godd, some say they aren't

well i think I'll install Oracle or DB2 on my box and have a look at them 🙂

[[deleted]]

Foreign keys are very important if you care about data consistency.
You have needed them, but you never thought you did. 🙂

[Anon]

Well can they server me on thing:

If you have two tables, one with user groups and the other with users and if I delete all users of one group do they take care that the corresponding group (now with no users) is deleted?

Well this is my assumption about foreign_keys 🙂)

thx for your very competent help

[[deleted]]

No.
It works the other way round.
A group can have many users.
Users cannot belong to a group that does not exist, but a group can have zero users in it.

PS you don\'t want to automatically delete a group when it\'s empty.
Image you have a group with one user, and you want to move that user to a different group and replace him with another user....

[Anon]

Ok, finally 🙂) can you drop a lil exampe how to create such a foreigen key.

So my main interest is, whether you have to create them at the time you create the table or wheter you have to 'insert' them into your selects etc.

thx

[[deleted]]

Foreign keys are a part of the table structure, so you'd allways put them in a create-time.
You can ofcourse create the keys later if you wish.
You sometimes have to, because a foreign-key constraint can only be created if all fields that the key applies to exist.

Sample of a foreign key situation:

Clients table:
userid SERIAL PK
name char(40)

Orders trable:
orderid SERIAL PK
userid INTEGER foreign key referencing Clients
orderdate DATE

In this case it's impossible to create orders for non-existing clients,
and it's impossible to delete clients that still have orders.