473,320 Members | 1,838 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Using foreign key

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.
Jan 30 '08 #1
5 1716
rpnew
188 100+
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
  1. 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;
  2.  
Jan 30 '08 #2
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.
Jan 30 '08 #3
rpnew
188 100+
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
Jan 30 '08 #4
Thank you for your help.
Jan 30 '08 #5
rpnew
188 100+
Thank you for your help.
Welcome...
I hope what i said you got that and its working for you....
Anyways... post back on forum whenever you face problems......

Regards,
RP
Jan 30 '08 #6

Sign in to post your reply or Sign up for a free account.

Similar topics

0
by: dcp | last post by:
I just installed the 4.1.0-alpha-max-nt version of MySql and have just started playing around with it. My first test was to try to create a couple of tables, one with a foreign key constraint. ...
2
by: geoff | last post by:
The table creation script(at the end of this post) works fine on 4.0.1-alpha-win, but the foreign key constraints fail on 4.0.15-win. I am starting the server with the same command for both...
9
by: Rathtap | last post by:
I want to use the Identity field (increment 1,1) as a primary key and have a unique constraint on my other field which is of type char. I am worried that related data in other tables may lose...
31
by: Robert Brown | last post by:
Let's say I have a type hierarchy: (just an example) the general entity customer: CREATE TABLE customer(customer_id int, customer_name varchar(250), customer_type int) three specific...
10
by: teddysnips | last post by:
Application is a Work Tracking/Timesheet database. The increments of work are stored in the TimesheetItem table. This contains, inter alia, the Work Code, the Start and the Duration that the...
1
by: Robert Fitzpatrick | last post by:
I am running PostgreSQL 7.4.5 and have a trigger on a table called tblriskassessors which inserts, updates or delete a corresponding record in tblinspectors by lookup of a contact id and license...
10
by: Shawn Chisholm | last post by:
Hi, I am trying to deal with a deadlock situation caused by foreign key references on insert and I was wondering if anyone knows what order the foreign keys are locked (or evaluated) in for a...
5
by: Rico | last post by:
Hello, I have a relationship that I'd like to modify using DAO. Right now the Cascading updates are off, but I'd like to add them and I'm not sure the best way to do that. Any ideas? ...
9
by: sonal | last post by:
Hi all, I hv started with python just recently... and have been assigned to make an utility which would be used for data validations... In short we take up various comma separated data files for...
2
by: Chris | last post by:
I have a database column that stores a comma delimited list of foreign keys. Would someone show me how to do a join using the values from a list stored within a record? For example, a record in ...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.