Regan wrote:
> Hello,
>
> I am fairly new to databases and hopefully have an easy question for
> you. I am setting up a database using a program DBDesigner 4. This
> database includes some many to many relationships. When I imported
> the tables this program created I get an error on the many to many
> bridge table in phpMyAdmin. It reads:
>
> ! PRIMARY and INDEX keys should not both be set for column
> `sd_church_id`
>
> I understand that since sd_contacts_id and sd_worksite_id are PRIMARY
> keys they do not need to be INDEX's also. Will removing
>
> INDEX sd_contacts_has_sd_worksite_FKIndex1(sd_contacts_i d),
> INDEX sd_contacts_has_sd_worksite_FKIndex2(sd_worksite_i d)
>
> break the many to many relationship or is that unnecessary code?
> Thanks!
>
>
> Here is some SQL this program generated:
>
> CREATE TABLE sd_contacts (
> sd_contacts_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
> sd_city_id INTEGER UNSIGNED NOT NULL,
> first_name VARCHAR(20) NOT NULL,
> last_name VARCHAR(20) NOT NULL,
> street_address VARCHAR(100) NULL,
> postal_code VARCHAR(7) NULL,
> phone_number VARCHAR(10) NOT NULL,
> mobile_number VARCHAR(10) NULL,
> email_address VARCHAR(50) NULL,
> PRIMARY KEY(sd_contacts_id),
> INDEX sd_contacts_FKIndex1(sd_city_id)
> );
>
> CREATE TABLE sd_contacts_has_sd_worksite (
> sd_contacts_id INTEGER UNSIGNED NOT NULL,
> sd_worksite_id INTEGER UNSIGNED NOT NULL,
> PRIMARY KEY(sd_contacts_id, sd_worksite_id),
> INDEX sd_contacts_has_sd_worksite_FKIndex1(sd_contacts_i d),
> INDEX sd_contacts_has_sd_worksite_FKIndex2(sd_worksite_i d)
> );
>
> CREATE TABLE sd_worksite (
> sd_worksite_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
> sd_city_id INTEGER UNSIGNED NOT NULL,
> street_address VARCHAR(50) NULL,
> postal_code VARCHAR(7) NULL,
> PRIMARY KEY(sd_worksite_id),
> INDEX sd_worksite_FKIndex1(sd_city_id)
> );
>

The relationship is not in the index. An index enforces uniqueness and
allows faster access to the rows (i.e. table scan not required) but does
not affect relationships between tables.

Those relationships are what foreign keys are all about.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================