Please give me example or link for these examples

linux1880 · #1 (**permalink**) 08-02-10, 08:24

Dear friends,

Can anyone please give me link or tutorials for 1-to1 and 1-to-many mysql simple example, tutorials or links please ? I tried google but I couldn't find simple ones. Please help.

r937 · #2 (**permalink**) 08-02-10, 09:20

to begin with, a true one-to-one relationship is not possible, it will always be one-to-zero-or-one

Code:

CREATE TABLE users
( id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT
, name VARCHAR(99) NOT NULL
, hatsize DECIMAL(4,3) NOT NULL DEFAULT 6.875 -- six and seven eighths
);

CREATE TABLE user_resumes
( id INTEGER NOT NULL PRIMARY KEY 
, CONSTRAINT resume_user
     FOREIGN KEY ( id ) REFERENCES users ( id )
, resume BLOB NOT NULL
);

a user's rows cannot simultaneously be added to both tables (with or without one of them being an auto_increment)

a user is first added to the users table, and then subsequently added to the user_resumes table

since id is the PK of both tables, it is unique, so the relationship is one-to-zero-or-one

Code:

CREATE TABLE user_phones
( id INTEGER NOT NULL 
, phone VARCHAR(12) NOT NULL
, PRIMARY KEY ( id , phone ) 
, CONSTRAINT phone_user
     FOREIGN KEY ( id ) REFERENCES users ( id )
);

here a user can have multiple phone numbers (but the PK ensures that a given user cannot have the same number more than once)

so this is a one-to-zero-or-many relationship (the "zero" bit for the same reason as above)

linux1880 · #3 (**permalink**) 08-02-10, 11:59

thank you so much r937, it helped me a lot to understand. How can i use the same scene as many-to-many relationship ?

r937 · #4 (**permalink**) 08-02-10, 12:14

Quote:

Originally Posted by linux1880

How can i use the same scene as many-to-many relationship ?

Code:

CREATE TABLE websites
( id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT
, url VARCHAR(99) NOT NULL
);
CREATE TABLE users_own_websites
( user_id INTEGER NOT NULL
, site_id INTEGER NOT NULL
, PRIMARY KEY ( user_id , site_id )
, CONSTRAINT own_user
    FOREIGN KEY ( user_id ) REFERENCES users ( id )
, CONSTRAINT own_site
    FOREIGN KEY ( site_id ) REFERENCES websites ( id )
);

linux1880 · #5 (**permalink**) 08-02-10, 15:29

sorry for the dumb question again, I could not understand
CONSTRAINT own_user,
CONSTRAINT resume_user
and
CONSTRAINT phone_user

Are these CONSTRAINT important to relate two tables ? when for example FOREIGN KEY ( site_id ) REFERENCES websites ( id ) is relating two tables. What is CONSTRAINT and CONSTRAINT values for ?

r937 · #6 (**permalink**) 08-02-10, 15:35

a foreign key ~is~ a constraint, but assigning a user-defined constraint name is optional

by specifying the constraint name explicitly, you make it easier to remove the constraint (should it ever become necessary to do so) by referring to it using the constraint name you assigned, rather than letting the constraint name be assigned some default value by the database if you don't define it

linux1880 · #7 (**permalink**) 08-02-10, 15:50

Thank you so much sir. However while looking at MySQL :: MySQL 5.1 Reference Manual :: 13.6.4.4 FOREIGN KEY Constraints it says tables must be InnoDB to use foreign key constraints, while I did not specify engine, i think default is MyISAM. Will it work or I am getting wrong ?

r937 · #8 (**permalink**) 08-02-10, 16:14

that is correct, foreign keys are not enforced in myisam tables

however, the CONSTRAINT syntax may still be specified, and will be parsed for correctness(and then ignored)

also, you can change the default table type if you wish

linux1880 · #9 (**permalink**) 08-02-10, 17:54

Thank you so much, much appreceated. I did populate all of those tables with raw data on them, what mysql commands would be best to show results relating to column of one field to another fields, should i do joins commands etc or there are other commands. Sorry for too many questions.

r937 · #10 (**permalink**) 08-02-10, 18:03

Quote:

Originally Posted by linux1880

what mysql commands would be best to show results relating to column of one field to another fields

sorry, i do not understand "results relating to column of one field to another fields"

i think you want a join but it's pretty hard to tell from this general description

Quote:

Originally Posted by linux1880

Sorry for too many questions.

i think you've pretty well got the answer you wanted (about 1-to-1, 1-to-many, and many-to-many relationships)

please don't let this thread evolve into a long tutoring session on how to do everything with databases, okay?