Setting Relationships

Anon

HI!

I just installed MySQL 3.2 as well as the GUI. I'm pretty faniliar with all the tools BUT still cannot find that tool where I can set relationships. Do you guys know where this tool is?

Cheers!

Anon

I'm guessing that you have a Microsoft Access Background?

:-)

I'm no expert - and I could be wrong, but As far as I know there is no tool to do so... You just need to use foreign keys in your tables and set your code to find the foreign key and then insert it in the table.

I do remember reading something about a certain "special" type of table in mysql that can do something cool with foreign keys, but I don't know anything about it.

Anon

MySQL has no support for foreign keys ("relationships"). If you happen to use InnoDB tables, they have support for foreign keys, but no cascading - ie. useless.

Switch to Postgres if you want referential integrity...

Chris

Anon

MySQL supports foreign keys only so sql exported from other databases can be more easily read into MySQL. You can use FOREIGN KEY (name (column, [column2,. . .]), but it will not actually perform any action.

Anon

I am doing a university dissertation based on MYSQL and PHP and was wondering how Foreign Keys worked as my lecturer only understands how Oracle works with CONSTRAINT CUST_PK REFERENCES PRIMARY KEY etc. HOw would you do this in MYSQL?

Anon

Mysql do not support constaints. No control of relationship from the db.

This will be added in some of the 4.0.x releases.

Charley

Anon

So if it does not support constraints then how does the database know that I am implying that the primary key is being referenced from another table? I have developed this table by means of normalisation and in oracle you would simply give the constraint a name and then reference it. ???????????? thanks if anyone can help?

Anon

I do not understand you.

you have a table A and B

A( id, B_id, ... )
B( id, ... )

The field A.B_id ist a foreign key (in design) from B.id. This must know your programmers which design the queries, but the database do not interest that.
You as a programmer have to look that for every A.B_id there ist a record in B with the same id.
You (as the programmer) have to delete the related records in B, when you delete a record in A.

Greetings
Charley

Anon

Ok this is my coding ok!

CREATE TABLE AGENCY_MEMBER
( MEMBERID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
ETC,
ETC
);

CREATE TABLE SPECIALIST_AREA
( MEMBERID INT UNSIGNED NOT NULL, //ISNT THIS A FOREIGN KEY?
SPECIAL_AREA VARCHAR(20) NOT NULL

PRIMARY KEY (MEMBERID)//IS THIS HOW YOU REFERENCE IT? INFACT WHAT DOES THIS LINE DO? IS IT LIKE MULTI TABLES OR SOMETHING?
);

HELP? THERES A PINT IN IT FOR YOU IF YOU CAN HELP!

CHEERS SIMON

Anon

This is the same

because of sql compatibility MySQL support different variants of defing tables. MySQL is very tolerant in importing data with queries from other systems.

MEMBERID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY

and

MEMBERID INT UNSIGNED NOT NULL,

PRIMARY KEY (MEMBERID)

is the same (except auto_increment)

Hint: if you make an unload of a table (mysqldump) with the opteion create table statemtent you will get the sql create which is created from mysql.

Anon

Ok I understand that but I'm interested in foreign keys, and how mysql would recognise that i am refering to a field in a totally separate table! I am used to using oracle where the foreign key is given a constraint name and it therefore references say the primary key in a table CUSTOMERID for example.

Does this mean anything to you?

many thanks!

Anon

Oracle supports referencial integrity if you define there a relation between two tables, oracle controls these relations. It let not insert records with wrong foregn keys and it deletes relational records if you delete the 'parent' record or restricts the deletion (it depends of your definitions).

MySQL has today nothing of these mechanisms. So it has no use of knowing about foreign keys. It accept the foregn key statement in a create table statement for compatibility purpose but it ignore them.

If you want to select to tables which are related, you use a sql join:

SELECT * FROM A, B WHERE A.ID = B.A_ID

MySQL optimize the access to the db, uses index if it can. To check how it access you use the EXPLAIN statement.

EXPLAIN SELECT ...

It is possible to delete a record in A and NOT delete the referenced records in B. So you have to check yourself or delete them yourself.
In our last Project we use a Object for db access which does all these checks for us. We define the relationship in the object.

This is all the same like in Oracle, if you use no referencial integrity.
In the past, when RI was very new, a lot of developers didn't use these features and do it in the historical way.

I hope we understand us now. If not, give me please a good excample in Oracle what you mean and i will tell you how to do it in MySQL.

Greetings
Charley

rycamor

You have just bumped full force into the fact that MySQL is not a full RDBMS. You could call it a DBMS, but it doesn't have the "R" part ;-).

Seriously, the short story is: if you want an application that uses normalized tables in MySQL, you have to code in your foreign key constraints (or relationships) at the application level, rather than at the SQL level. What this means in practical purposes is that if your application code is not right, you could have orphaned records or worse. Quite simply, if you are inserting a record that you want to relate to another record, and your code has an error, MySQL will accept an incorrect foreign key without complaint. Thus, at the moment it is suicide to try to code a mission-critical application in MySQL.

When the other posters mentioned that MySQL can accept a foreign key constraint in SQL create statements, they meant that MySQL doesn't cause an error upon doing that, but the statement is only in there for documentation purposes.

However, to further muddy the waters, MySQL also uses more than one table type. They claim that for the InnoDB table type, they are in the beta phase for FK constraints, but honestly, it is still very basic, and doesn't support the features that you will find in Oracle. So the story in MySQL is a little confusing, unless you read the documentation for each table type carefully.

In fact, trying to compare MySQL to Oracle is like trying to compare DOS to Unix. Yes, on the surface they may seem to have many of the same functions, but one has much greater capability and a more solid underlying principle than the other.

Seriously, if you want an open source database that compares well to Oracle, you should check out PostgreSQL (www.postgresql.org).