Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2014
    Posts
    1

    Unanswered: 1-many relationship mysql

    I'm currently studying mysql and I need a One to many ralation 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..

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Why would you call tables "acc1_MyWatchlist" and "acc2_MyWatchlist"?

    The concept you seem to be refering to is foreign key constraints (https://dev.mysql.com/doc/refman/5.1...nstraints.html). You should pay attention to the CASCADE keyword too.
    George
    Home | Blog

  3. #3
    Join Date
    Jul 2014
    Posts
    3
    As user gvee says, you should read about table constraints. They specify rules for the data in a table.
    Constraints can be specified in CREATE TABLE statement and ALTER TABLE statement.
    First you should read about FOREIGN and PRIMARY key.

    PRIMARY KEY constraint identifies a row that is unique. Thanks to pk constraint it's possible to find appropriate value in a table more easily and quickly, because it clearly specifies values of other fields. In designing proccess, commonly 'id' value is added and marked as pk in table. It may happen that more than one value identifies a table occurrence. In that case you can create a compound key (it will contain two or more values).

    FOREIGN KEY ensures the referential integrity of data in one table to match values in another table. It means that foreign key in one table links to a primary key in another table.

    I believe this model presents situation that you described:

    Click image for larger version. 

Name:	dbforum-keys.png 
Views:	1 
Size:	6.3 KB 
ID:	16047
    https://my.vertabelo.com/public-mode...=4531&zoom=1.0

    In such a situation foreign key prevents from inserting a movie to your watchilst that doesn't exists in movie table.
    Keep in mind that names of columns are very important.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •