Primary Key and Secondary Key Relationships

Databases
1

Hello All,

What is the difference b/w primary key and secondary key?

Does anyone know how to relate a table say “business” to another table say “state” via state_id.

I have created the tables but to relate them is a problem…see below he codes I used.

for the business table we have…

CREATE TABLE business (
business_id int(11) NOT NULL auto_increment,
businessname text NOT NULL,
address1 varchar(255) NOT NULL default ‘’,
address2 varchar(255) NOT NULL default ‘’,
area varchar(255) NOT NULL default ‘’,
telephone1 varchar(255) NOT NULL default ‘’,
telephone2 varchar(50) NOT NULL default ‘’,
fax varchar(255) NOT NULL default ‘’,
email varchar(255) NOT NULL default ‘’,
url varchar(255) NOT NULL default ‘’,
state_id varchar(255) NOT NULL default ‘’,
subcat_id varchar(255) NOT NULL default ‘’,
PRIMARY KEY (business_id),
KEY state_id (state_id),
KEY subcat_id (subcat_id)
) TYPE=MyISAM AUTO_INCREMENT=1 ;

and the state table

CREATE TABLE state (
state_id int(11) NOT NULL auto_increment,
state_name varchar(255) NOT NULL default ‘’,
capital varchar(255) NOT NULL default ‘’,
state_title varchar(255) NOT NULL default ‘’,
PRIMARY KEY (state_id)
) TYPE=MyISAM AUTO_INCREMENT=1 ;

How best can I relate it???

2

to relate tables in mysql, you have to use InnoDB tables – it’s all explained in the mysql documentation

for example, here’s the relevant page in the 4.1 manual – http://dev.mysql.com/doc/refman/4.1/en/innodb-foreign-key-constraints.html

3

Hello, thanks for bringing me back to the manual…but do u have any ideas on how i can MODIFY the script to do what I want???

4

sure – first, change TYPE=MyISAM to TYPE=InnoDB, then include the correct indexes on the fields you want to relate, finally, add the FOREIGN KEY clause

:slight_smile: