creating a foreign key using ALTER TABLE

[weewizard]

Hi there

I have a database I want to add foreign keys using this:

ALTER TABLE tblProduct ADD FOREIGN KEY (fldProductCatID) REFERENCES tblProductCat(fldProductCatID)

I am using phpMyAdmin to do the query.

My question is:

When I export the exisitng schema will I see the foreign key query in with the 'Create Table' query?

Cheers
Richard

[jaswinder_rana]

when you export table from PHPMyAdmin you won't see those foreing key statements (as i already tried to do this) because MySQL doesnot support foreign keys and doesn't matter if you put those statements in. it won't affect how the tables in MySQL Works.

i Have MySQL 4.0.12. (so, i don't know if this is still true in the newer versions or not)

[weewizard]

when you export table from PHPMyAdmin you won't see those foreing key statements (as i already tried to do this) because MySQL doesnot support foreign keys and doesn't matter if you put those statements in. it won't affect how the tables in MySQL Works.

so does this mean i will not be able to create foreign keys within other tables?

or are they existing but not visible within the schema?

cheers

[jaswinder_rana]

you can but as far as i know it doesn't matter to MySQL(until newer versions, but many hosts haven't updated tehir MySQL to newest versions). It doesn't mattert because there will be no relation. Ofcourse you would still the same thing like foregin keys and joins on table. THE ONLY DIFFERENCE WOULD BE for eg

user table
userid | username | password
1 | test1 | test1
2| test2 | test2

user_log table
userid | date | in | out
1 | today | 1 | 0
1 | today | 0 | 1
4 | today | 1 | 0

now in user log you can enter a userid 4 even though it doesn't exists BUT if you were using Oracle (for eg.) it would complain that because userid is foreign key therefore the userid=4 does not exist in user table so this can't be done

MySQL wouldn't complain about that. that's only drawback otherwise you can (i thin ki should you should) do all the joins like
select username from user,user_log where user.userid=user_log.userid and user.userid=1

hope i am being clear

[weewizard]

Im having all sorts of problems, I am to use MySQL and PHP for my project, below is the datamodel. Any ideas how I would incorporate the schema relationships, foreign keys etc into this?

Cheers

Richard

[jaswinder_rana]

well i am not a database guru but your schema is not good. because once one of my instrcutor told me whenever you see a schema which is making a CIRCLE , THINK ABOUT IT. he said you should make a schema that should not make a circle.
so, based on that i said you should make some changes to database schema. there are other database gurus who would help you for sure.

[r937]

there's nothing wrong with "circles" in data models

i'll give you a simple example -- "employee works in department" is a zero-or-many-to-one relationship, "department is managed by employee" is a one to zero-or-one relationship, voila, circle with two entities and two relationships, but the design is perfectly valid

richard, i think you have an extra table in there, the interests table seems wrong

if there's a many-to-many relationship between customers and product categories, there should be only one relationship table between them

[pdxi]

when you export table from PHPMyAdmin you won't see those foreing key statements (as i already tried to do this) because MySQL doesnot support foreign keys and doesn't matter if you put those statements in. it won't affect how the tables in MySQL Works.

i Have MySQL 4.0.12. (so, i don't know if this is still true in the newer versions or not)

That's simply untrue. MySQL supports foreign key constraints in all versions of MySQL after 3.23.44, provided that all tables are of the InnoDB type.