Reenforcing Table Relationships in MySQL

Anon

I am creating a links page which is stored in a table / database. Each link is spilt into categories.

So, one link category has many links.

For example, a category of "Builders" could have "DIY store" in it, and so on.

Thus I have set up both a link category table and a links table:

tbl_linkcat {
linkcat_id
linkcat_name
}

tbl_links {
linkcat_id
link_id
link_name
link_url
}

now, this implies that there is some link between linkcat_id in the tbl_linkcat table and the tbl_links table...

But there is no link there...how do you reinforce and test relationships in mysql?

[deleted]

All you an do in MySQL is use the InnoDB table handler. Then you can enforce a primary-key/foreign-key relationship so that linkcat_id in the tbl_links must exist in tbl_linkcat. But this relations is only a check, it will prevent you from putting illegal data in or removing data that will make other data illegal.

You cannot use the relationship to automatically delete related records (cascading).

If you need proper relations, you have to move to PostgreSQL.

A forum, a FAQ, email notification, what else do you need?

Time to YAPF!: http://www.hvt-automation.nl/yapf/

Anon

That's a bit silly, I thought mysql was meant to be this grand, brilliant database - much better than Access 97, etc.

I'm not really sure what InnoDB or PostgreSQL is. Is InnoDB in Mysql?

I've been reading the documentation for phpmyadmin, it mentions something about indexes...what are indexes? I know what primary keys are, normalisation is, etc.

Is there a way of using indexes for enforcing relationships between two tables?

But I do need proper relationship handling between my tables, mainly because most of it is like a tree with leafes..

Anyway, thanks for your help anyhow...

[deleted]

"much better than Access 97,"

Anything is better than access 97 🙂

InnoDB is a table handler for mysql, it integrates into mysql (check the manual)

PostgreSQL is a completely different product that would replace mysql completely.

About indexes; read the mysql manual, it has plenty info to feed on.

Indexes have nothing to do with relations at all.

You can build a tree with leaves using MySQL with InnoDB, but you can't use cascading deletes etc to ensure that you always remove and entire tree/branche, and not just a few parent nodes (leaving orhan nodes and leaves)

You have to write a script with multiple queries to safely remove a tree.

A forum, a FAQ, email notification, what else do you need?

Time to YAPF!: http://www.hvt-automation.nl/yapf/

mikeq

Hi,

The relationship is implied, you need to make sure any application you build enforces the referential integrity between the tables. i.e. if your application deletes a record in tbl_linkcat it must first delete any related records in tbl_links.

You don't need the database to do the integrity checking unless you will let users insert/update/delete directly onto the database.

Mike

Anon

Hehe - if only I had a dollar for everyone who's realised that after using MySQL for more than a month!!!!

MySQL vs. Postgres is kinda like VHS vs. Beta. The better product doesn't get the publicity and everyone ends up using the inferior product with all the publicity!

Just wait until you try doing a subselect or a transaction in MySQL - then you'll get a shock!

Chris

[deleted]

Actually, mysql with InnoDB does support transactions rather succesfully. 🙂