faqts : Computers : Databases : MySQL

+ Search
Add Entry AlertManage Folder Edit Entry Add page to http://del.icio.us/
Did You Find This Entry Useful?

98 of 216 people (45%) answered Yes
Recently 5 of 10 people (50%) answered Yes

Entry

How do I create a relationship between two tables?

Feb 12th, 2008 04:27
dman, The Guru, Narendra Jain, Sam Psaltakis, http://sturly.com


A relationship is created between two tables by a primary key - foreign
key concept. In short, a primary key in one table would be a foreign key
in another table. 
e.g. customer_id is a foreign key in invoice table whereas it is primary
key in the customer table.
It is advisable to have integers as primary / foreign keys for the best
and optimal database performance.
__________________________________
The Guru explains:
Lets say There are two tables. Table1 and Table2. And you want to add 
reference of Field USER_ID from Table1 to USER_NEW_ID of Table2. 
Alright?
Now there are two scenarios. 
1) You establish the relationship while creating the tables.
2) You establish the relationship after the tables are created.
Lets go one by one.
1) Case: You establish the relationship while creating the tables -
CREATE TABLE `TABLE1` (
  `USER_ID` int(11) NOT NULL auto_increment,
  `FIRST_NAME` varchar(255) NOT NULL default '',
  `LAST_NAME` varchar(255) NOT NULL default '',
  CONSTRAINT `TABLE1_ibfk_1` FOREIGN KEY (`USER_ID`) REFERENCES 
`TABLE2` (`USER_NEW_ID`),
);
CREATE TABLE `TABLE2` (
  `USER_NEW_ID` int(11) NOT NULL auto_increment,
  `FIELD2` varchar(255) NOT NULL default '',
  `FIELD3` varchar(255) NOT NULL default '',
);
By using these two SQL statements, you will have two tables created 
with relationship between these two tables.
2) Case: You establish the relationship after the tables are created.
First thing you have done is created both the tables, Alright?
Now you run this SQL statement to add the relationship between those 
two tables.
ALTER TABLE TABLE1
ADD [CONSTRAINT TABLE1_ibfk_1] FOREIGN KEY (`USER_ID`)
REFERENCES TABLE2 (`USER_NEW_ID`)
I hope this explains you in details.
The Guru
http://techiecorner.blogspot.com
____________________________________________________