SQL Doubt regarding foreign keys

Databases
1

Hi all,

I have few doubts which I want to clear regarding referential constraints.

Lets say I have three tables

a) Users
b) Gallery
c) Images

a) Users has following columns:

usr_id int
first_name nchar(30)
last_name nchar(30)
email_id nchar(45)
password nchar(25)
gender char(1)
dob date

In Users table I have set usr_id as the primary key.

b) Gallery has following columns:

gallery_id int
user_id int
gallery_name nchar(50)
gallery_description nchar(250)
date_created datetime

In Gallery table I have set gallery_id as the primary key.

c) Images table has following columns:

img_id int
gallery_id int
img_path nvarchar(200)
img_caption nvarchar(250)

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 ??

thanks

…:: peace ::…
Jeba

2

thanks for clearing it .

3

that is correct :slight_smile:

4

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

:slight_smile:

5

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 :slight_smile:

6

The link between Users and Gallery is user_id.
The link between Gallery and Images is gallery_id.

7

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