I have few doubts which I want to clear regarding referential constraints.
Lets say I have three tables
a) Users has following columns:
In Users table I have set usr_id as the primary key.
b) Gallery has following columns:
In Gallery table I have set gallery_id as the primary key.
c) Images table has following columns:
in Images table I have set img_id as the primary key.
now my doubt is I want to link these tables using foreign key.
Users -> Gallery -> Images
which is the best column in gallery to be a foreign key to the Users table.
In Users table usr_id is my primary key and in gallery it is gallery_id.
So should I link usr_id of user table with the usr_id in Gallery table ?? i m little confused.. because by default shouldn't it be the primary key that gets linked ??
..:: peace ::..
that is correct
the primary key is referenced by the foreign key, while the foreign key references the primary key
it's pretty clear when you look at the syntax for declaring the foreign key
thanks guys I am a bit more clear now.
both means same right ? I mean at the end it is the primary key of the parent table gets referenced by the foreign key of the child table. pls correct me if I am wrong
The link between Users and Gallery is user_id.
The link between Gallery and Images is gallery_id.
and if you want to allow for a given image to belong to more than one gallery (e.g. a picture of a house on fire can belong to the Houses gallery and also to the Fires gallery) then you'd want an additional table -- but we can go over those foreign keys later, after you are comfortable with the keys so far