Using foreign key

gubbachchi

Hi,

How to index foreign key between two separate databases.
I have indexed foreign key between tables. Here is the sql query I have used

CREATE TABLE User_Login(user_id int(10) UNSIGNED NOT NULL, user_name char(15) NOT NULL, user_pwd char(10) NOT NULL, PRIMARY KEY(user_id)) TYPE=INNODB;

CREATE TABLE Per_Info(user_id int(10) UNSIGNED NOT NULL, sms_date datetime NOT NULL, INDEX(user_id), FOREIGN KEY(user_id) REFERENCES User_Login(user_id))ENGINE=INNODB;

where the 2 tables User_Login and Per_Info are in the same database database1, but how to index foreign key when the 2 tables are in the seperate databases database1 and database2.

Can anyone help me.

rpnew

Quote:

Originally Posted by gubbachchi

Hi,

How to index foreign key between two separate databases.
I have indexed foreign key between tables. Here is the sql query I have used

CREATE TABLE User_Login(user_id int(10) UNSIGNED NOT NULL, user_name char(15) NOT NULL, user_pwd char(10) NOT NULL, PRIMARY KEY(user_id)) TYPE=INNODB;

CREATE TABLE Per_Info(user_id int(10) UNSIGNED NOT NULL, sms_date datetime NOT NULL, INDEX(user_id), FOREIGN KEY(user_id) REFERENCES User_Login(user_id))ENGINE=INNODB;

where the 2 tables User_Login and Per_Info are in the same database database1, but how to index foreign key when the 2 tables are in the seperate databases database1 and database2.

Can anyone help me.

Hi,
You can use the same syntax only change you need to make is to how to refer the table.. like in your above example if your user_login table is in db1 and per_info is in db2. so your second create query will be something like this.......

Expand|Select|Wrap|Line Numbers

 
CREATE TABLE Per_Info(user_id int(10) UNSIGNED NOT NULL, sms_date datetime NOT NULL, INDEX(user_id), FOREIGN KEY(user_id) REFERENCES db1.User_Login(user_id))ENGINE=INNODB;

gubbachchi

Thank u for the solution.

I have got one more doubt regarding the JOIN query. Here I am not using Foreign key.

I have used this query to join 2 tables in the same database and select the data depending on the condition:

SELECT first_name from User_Login Natural JOIN User_Info_Main WHERE user_id='1';

In this case the 2 tables User_Login and User_Info_Main are in the same database

But how will I use join queries to fetch data from 2 tables when the 2 tables are in the different databases, i.e. User_Login in database1 and User_Info_Main in database2.

rpnew

Quote:

Originally Posted by gubbachchi

Thank u for the solution.

I have got one more doubt regarding the JOIN query. Here I am not using Foreign key.

I have used this query to join 2 tables in the same database and select the data depending on the condition:

SELECT first_name from User_Login Natural JOIN User_Info_Main WHERE user_id='1';

In this case the 2 tables User_Login and User_Info_Main are in the same database

But how will I use join queries to fetch data from 2 tables when the 2 tables are in the different databases, i.e. User_Login in database1 and User_Info_Main in database2.

Hi,
As i told you earlier.. you can do the same....... just refer your table in following manner ... like databasename.tablename ... if you are logged in in some database then dont need to mention tables in that database as above simple access them withe their name...... In short if you want to access table in other table refer them in above manner ..

Regards,
RP