CODEWITHSUNDEEP
oracle-databaseforeign-keysprimary-keycomposite-primary-keycomposite-key
user609511
user609511

Reputation: 4261

how to add a foreignkey to a composite primary key

I have 2 tables: T_CAMPAGNE_SMS_REPARTITION and T_CAMPAGNE_SMS_EXECUTION.

In T_CAMPAGNE_SMS_REPARTITION there are 2 primary keys.

enter image description here

In T_CAMPAGNE_SMS_EXECUTION there is 1 primary key and 4 foreign keys.

enter image description here

How can I add the 4th foreign key CLE_PARTITION of T_CAMPAGNE_SMS_EXECUTION?

I have tried this:

ALTER TABLE "T_CAMPAGNE_SMS_EXECUTION"
    add constraint FK_REPARTITION foreign key("CLE_REPARTITION")
    references "T_CAMPAGNE_SMS_REPARTITION"("CLE_REPARTITION");

but I got this error:

Erreur commençant à la ligne: 10 de la commande -
ALTER TABLE "T_CAMPAGNE_SMS_EXECUTION" 
  add constraint FK_REPARTITION foreign key("CLE_REPARTITION") references "T_CAMPAGNE_SMS_REPARTITION"("CLE_REPARTITION")
Rapport d'erreur -
Erreur SQL : ORA-02270: pas de correspondance de clé primaire ou unique pour cette liste de colonnes
02270. 00000 -  "no matching unique or primary key for this column-list"
*Cause:    A REFERENCES clause in a CREATE/ALTER TABLE statement
           gives a column-list for which there is no matching unique or primary
           key constraint in the referenced table.
*Action:   Find the correct column names using the ALL_CONS_COLUMNS
           catalog view

I have tried via a design like this:

enter image description here

but I still get the same error message.

Upvotes: 2

Views: 2982

Answers (3)

philipxy
philipxy

Reputation: 15118

A table has one or more CKs (candidate keys): unique column sets that don't contain smaller unique column sets. At most one CK can be declared PK (primary key) per table by PRIMARY KEY; declare the others via UNIQUE NOT NULL.

You have one PK, with two columns. Having more than one column, it is composite.

An SQL FOREIGN KEY is list of columns whose values appear elsewhere as SQL PRIMARY KEY or UNIQUE NOT NULL. And the referenced list has to be declared unique even if it contains another declared unique column set. Your referenced list (CLE_REPARTITION) is not declared unique. So exactly as the error message says, there is no matching unique or primary key constraint in the referenced table.

Maybe you want two one-column non-composite PKs instead of the 2-column PK. Maybe just (CLE_REPARTITION) is a CK/PK. Maybe you want a composite SQL FK instead of the 1-column one. Maybe you want T_CAMPAGNE_SMS_REPARTITION (CLE_REPARTITION) subrows to be T_CAMPAGNE_SMS_EXECUTION (CLE_REPARTITION) subrows even though they're not unique there. That's not a FK. It's an IND (inclusion dependency). It requires a trigger to enforce.

These constraints are determined by what a row says when it is in a table in a business situation and what situations/states can arise given business rules. Determine exactly what the constraints on your tables are and declare them per above.

Upvotes: 0

JulHaus
JulHaus

Reputation: 81

If you don't want to add a column in T_CAMPAGNE_SMS_EXECUTION then create a technical primary key on T_CAMPAGNE_SMS_REPARTITION. Something like T_CAMPAGNE_SMS_REPARTITION_ID which is a simple number linked to a sequence (or an IDENTITY column if you have an Oracle 12, see the doc here https://oracle-base.com/articles/12c/identity-columns-in-oracle-12cr1).

Change the Primary key contraint to a unique constraint on T_CAMPAGNE_SMS_REPARTITION to keep the behavior, and create a new column in T_CAMPAGNE_SMS_EXECUTION to link in to the newly created column.

The thing is that you can't reference only one part of the composite PK because you have no way to be sure that it's unique.

Hope this helps

Upvotes: 1

ggradnig
ggradnig

Reputation: 14169

You have a composite primary key made up of two columns, but in your FOREIGN KEY you are only referencing one of those two columns.

Put another column in your T_CAMPAGNE_SMS_EXECUTION for the second PK column with the name CLE_LOT_REPARTITION

After that, change your FOREIGN KEY constraint like so:

ALTER TABLE "T_CAMPAGNE_SMS_EXECUTION" 
  add constraint FK_REPARTITION foreign key("CLE_REPARTITION", "CLE_LOT_REPARTITION") references "T_CAMPAGNE_SMS_REPARTITION"("CLE_REPARTITION", "CLE_LOT_REPARTITION");

Upvotes: 0

Related Questions