Is there a way to pair foreign keys to match?
E.g. The user_id
and subs_id
fk pair doesn't match in the product table but was allowed to be added.
Table: user
| user_id (PK) | first_name | last_name |
| -------------| ------------|------------|
| 000001 | David | Hawk |
| 000002 | Ali | Abdullah |
Table: subscription
| user_id (FK) | subs_id (PK) | subs_status | total_cycles |
| -------------| -------------|-------------|--------------|
| 000001 | ABC_123456 | ACTIVE | 4 |
| 000002 | CDE_654321 | CANCELLED | 8 |
Table: product
| user_id (FK) | subs_id (FK) | product | plan | product-key (PK) |
| -------------| ---------------|-------------|--------|------------------|
| **000001** | **CDE_654321** | Product-A | Pro | A5CD-8Z62-X2D4 |
| **000002** | **ABC_123456** | Product-B | Plus | WFE7-71W4-Z64D |
If I understand correctly, you have two foreign keys, one on the user_id
and one on the subs_id
. Instead, you need to have a single foreign key on the combination of the two:
ALTER TABLE product
ADD CONSTRAINT prodcut_subscription_fk
FOREIGN KEY (user_id, subs_id)
REFERENCES subscription(user_id, subs_id)