Mysql relationship

[Raymart]

I'm new to mysql and I need a ralationship tutorial.. like for example I made a table called MovieList with 1 column name TitleMovie and now I want to create another table called acc1_MyWatchlist and inside of it was also a TitleMovie..my question is I want insert a value in acc1_mywatchlist but only the available values in Movielist's TitleMovie and If I tried to create another table acc2_mywaychlist it should be the same as the first table and if I tried to edit some values in the MovieList table it should be affect all the table that has the same column TitleMovie....

in Access database I just use the lookup wizard but ofcourse mysql is differnt so can you give me some examples of this query..

[Traevel]

You'll want to define a foreign key constraint on the acc1 and acc2 tables with a reference to the movie id.

Something along the likes of

CREATE TABLE MovieList(
    id int NOT NULL,
    title varchar(255) NOT NULL,
    PRIMARY KEY(id)
)

CREATE TABLE acc1_mywatchlist(
    movie_id int NOT NULL,
    FOREIGN KEY(movie_id) REFERENCES MovieList(id)
)

It's common to use numerical id's as keys since you require a unique reference, imagine what would happen to your table relations if it allowed two movies to share the same title.

To answer the second part, if you want the tables with the foreign keys to update you can add ON UPDATE CASCADE or ON UPDATE RESTRICT. The same goes for deletion ON DELETE RESTRICT or ON DELETE CASCADE. So for instance

CREATE TABLE acc1_mywatchlist(
    movie_id int NOT NULL,
    FOREIGN KEY(movie_id) REFERENCES MovieList(id) ON UPDATE CASCADE ON DELETE RESTRICT
)

to allow updates of the constraint but to restrict deletion. In other words when there are still movies on a watchlist you can not delete the movie entry in MovieList. If you however wanted to change the key number (even though that's not good practice) it would be changed in the acc1 table as well.

However, working with numerical keys has the advantage that changes to the MovieList table would have far less of an effect on the watchlist tables. After all, if it's using the numerical key as reference and not the title you can change the title and the watchlist tables would still have the proper reference (through the keys). So the first example might already be enough to achieve the result you're after.