CODEWITHSUNDEEP
sqlpowerdesigner
Ali Bdeir
Ali Bdeir

Reputation: 4365

How to create a Composite Primary Key in SAP PowerDesigner

I have a join table and I would like to create a composite primary key for it, like so:

PRIMARY KEY ('table1fk' , 'table2fk')

How would I do this via the SAP PowerDesigner table/column editor?

Upvotes: 0

Views: 1116

Answers (2)

Martijn van der Jagt
Martijn van der Jagt

Reputation: 524

Although this is not a recent question the subject is still relevant. SQL was standardised in 1992 (SQL92), PowerDesigner latest update is from 2020, and the issue of composite foreign keys is still badly understood. The answer below is i.m.o incomplete because it does not fully explain the usage of a composite key in power designer.

A composite primary key is required for a composite foreign key.

A composite primary key occurs if in the table of the primary key two or more columns are required to designate a unique record. An example of this is the situation where in the primary key table there are versions of a proposed transaction. Only the transactionnumber and the versionnumber combined indicate the unique record.

The only way to point to a unique record from a foreign table is to point from two (or more) columns in the foreign table to the two (or more) columns in the primary key table.

Thus the foreign key becomes a composite foreign key.

In PowerDesigner this situation is created by indicating both columns as primary key in the primary key table:

  1. ID Bigint
  2. TX_code Bigint PK
  3. Seq_nr Bigint PK

Create a foreign key relation to this table, and look in the Properties window of the reference (the line from one table to the other). Switch to the 'Joins' tab of this window, and you will see two columns of the primary key table in this one reference. If the column names of the foreign table are identical to those of the primary key table they are correctly filled in already. If not then you have to select the right columns to fill the join with the correct data.

Upvotes: 0

pascal
pascal

Reputation: 3365

After joining your table to the other tables, you mark the Foreign Key migrated from the other tables, as Primary Key.

enter image description here

Which gives the following SQL script:

create table PARTICIPATION (
POLL_ID              int                  not null,
USER_ID              int                  not null,
primary key (POLL_ID, USER_ID),
foreign key (POLL_ID)
      references POLL (ID),
foreign key (USER_ID)
      references "USER" (ID)
);

Upvotes: 1

Related Questions