MySQL and Relational DB?

saidbakr

Is there any way to make MySQL able to do the following:
Suppose:
Two tables named as tab1 and tab2
tab1 includs: ID, name, operation.
tab2 includs name(as primary key), Tel_NO.
So I want to be able to Join the two tables where if I execute "SELECT * from tab1", all fields in tab1 and the field of Tel_NO will be displayed. I want to do this because in the tab1 the field name will be repeated many times for the same record, and I don't want to change all these records when only the Tel_NO of certain name is only changed.
regards😕

nnichols

SELECT tab1.*, tab2.Tel_No
FROM tab1, tab2
WHERE tab1.name=tab2.name

Hope this helps 😉

saidbakr

Ok, This's good for select. But, suppose for insert, i.e. If I insert a value for field name in tab1 which has no any the same value for name in tab2(in which name is aprimary key). In Ms Access, in the presence of relation between the two tables this will be forbiddin. So I ask How to do like this using MySQL.
Regards🆒

nnichols

Sorry I don't quite understand the question. Can you ask again?

nnichols

Foreign Key and Primary Key Constraints don't really exist in MySQL yet, instead this must be handled through application logic.

It depends on what you want to do. If you need values for "name" in both tables with associated data then make sure that your form inputs data in both tables.

Hope this helps 😉

ratass2002

I am not sure what you want to do, but I see a flaw in your tables(unless you assumed something here). You should use ID(as the primary) in Tab2, because you are certain that it's unique, also it's a much smaller variable. Also why would you want to have a table for the telephone number anyways(unless it's because of the column "operation" in Tab1.)?

Anyhow I didn't understand your question either, what is your concern? Or what do you want to do exactly?

saidbakr

Yes that's so good and usefull. So The application will do this process. Ok, but you said that "Foreign Key and Primary Key Constraints don't really exist in MySQL yet" and I will focus on the word YET. so do you have any idea that MySQL will include this feature in the future? or let we say in the near future.🙂

saidbakr

I have found this article in MySQL documentation soon
http://www.mysql.com/doc/en/example-Foreign_keys.html
For first idea, :rolleyes: I think it says that MySQL may relates two tables like Ms Access. I invite you to read it then discus about it.😃

nnichols

Yes that is true, but only of the InnoDB table handler.

I think it will be available for native MySQL table types in version 4.1 due some time later this year. You will need to do some searching on the mysql site.