0

I would like to know if it is right to link a relationship table with other tables. I tried it in PowerDesigner, but it give me a warning about it. However: I have two table called "Client" and "Staff" in Many-to-Many relations. In the given relationship table I linked two other tables in 1-to-Many realtions. The relationship table now has two primary keys (primary key of staff and primary key of client) and 2 foreign key. It's a correct procedure? I have this situation: enter image description here

The warning message is:

Category Check Object Location Table Index Index inclusion Index 'generate rent history.GENERATE_RENT_HISTORY_PK' includes 'GENERATE_RENT_HISTORY_FK' ::generate rent history

Giacomo Brunetta
  • 1,409
  • 3
  • 18
  • 38

1 Answers1

1

It's valid for a table to have a composite primary key which consists of two columns, and for each of those columns to be a foreign key. And it's valid to have other columns which are also foreign keys.

I'm not familiar with PowerDesigner, but I suspect the warning message isn't about the foreign key constraints, but concerns redundant indexes.

To support the PRIMARY KEY constraint, there's going to be UNIQUE INDEX on (idstaff,idclient).

To support the FOREIGN KEY constraints, we would need an index with leading columns of idstaff, and another index with a leading column of idclient.

But an index on (idstaff) is redundant.

We already have an index with a leading column of idstaff... the primary key index.

As far as the table definition, these two indexes are sufficient to support the foreign key constraints referencing staff and client:

 PRIMARY KEY (idstaff,idclient)
 KEY generate_rent_history_IX1 (idclient)

There's no need to add an index like this:

 KEY generate_rent_history_IX4 (idstaff)

I suspect PowerDesigner is being instructed to generate that redundant index, and is issuing a warning that the index is not actually needed.

(I'm just guessing at the order of the columns in the PRIMARY KEY. If the columns are the other way around with idclient as the leading column, then we need the index on idstaff column, and the index on idclient column would be redundant.)

spencer7593
  • 106,611
  • 15
  • 112
  • 140
  • Sorry for the late, but isn't it an error, right? if I export it to MS Access it works correctly? – Giacomo Brunetta Feb 09 '17 at 14:44
  • ah, in your opinion is this database's part in the second normail form? I think yes (note that the relationship table represent the order of client, taken by the staff, in fact there are two primary key: idclient and idstaff. Foreign keys represent the orders of the client, written by staff) – Giacomo Brunetta Feb 09 '17 at 15:25
  • To be more precise, the primary key of `generate_rent_history` contains *two* columns. It's a *composite* primary key. Those *two* columns are components of exactly *one* primary key constraint on the table. – spencer7593 Feb 09 '17 at 20:27