CODEWITHSUNDEEP
sqloracle-databaseforeign-keys
jacen44
jacen44

Reputation: 168

Referencing one column of a composite primary key as a foreign key

In the table A, I've got a composite of 3 fields as a primary key and in the table B I've got one of the composite (which is not unique). I'd like to reference with the table A to ease the delete (with ON DELETE CASCADE).

So, am I compelled to put the other two fields in the table B and reference with this composite or is there another solution?

Upvotes: 4

Views: 6484

Answers (2)

GriffeyDog
GriffeyDog

Reputation: 8376

You could create a new single-column (probably sequence-generated) primary key in table A and make the existing composite key a unique key instead of a primary key. Then reference your new primary key from table B.

Upvotes: 1

Justin Cave
Justin Cave

Reputation: 231671

If you want to create a foreign key constraint between the two tables, the child table would have to have all the columns that comprise the primary key constraint on the parent table (which is one of the reasons that I would discourage the use of composite primary keys). If you want Oracle to automatically delete a child row when then parent row is deleted, you need a foreign key constraint with ON DELETE CASCADE. So your options are

  • Add all the columns that comprise the primary key of the parent to the child.
  • Redesign the parent table so that there is a single-column primary key (presumably a synthetic key) and use that in the child table.
  • Write code that implements the deletes yourself.

Upvotes: 3

Related Questions