0

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   |
Mureinik
  • 297,002
  • 52
  • 306
  • 350
Andy Fazulus
  • 35
  • 2
  • 9
  • Made a simple design error there. There should be no `user_id` in the `product` table. There should only be one product row per product. With your design you would need one product row per each user that subscribed. – RiggsFolly Sep 14 '21 at 08:57
  • Also there should be no `subs_id` in the product row, for the same basic reason. Your `subscriptions` table is basically a link table between User and Product – RiggsFolly Sep 14 '21 at 08:58
  • You dont need user_id column in product. You can use the subs_id and get the user id associated to the subscription – Shreyas Prakash Sep 14 '21 at 08:58
  • Also I would place the `product_key` which appears to be specific to a single users subscription to a product into the subscriptions table and make an `id` column the PK and AutoIncrement – RiggsFolly Sep 14 '21 at 08:59
  • Whatever you are trying to say, you are not saying it. Use enough words, sentences & references to parts of examples to clearly & fully say what you mean. [ask] [mre] [Help] – philipxy Sep 14 '21 at 09:32
  • @RiggsFolly The `subscription` table is being used for storing data from REST API calls while the `product_id` is actually a UUID generated by server-side. I'd rather not mixed it up. I think I'll go with @ShreyasPrakash suggestion, I see his point about `user_id` being redundant. I just designed it like that for ease of use I suppose, so that I can make direct query using either `subs_id` or `user_id`. Thanks everyone. – Andy Fazulus Sep 14 '21 at 10:46

1 Answers1

1

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)
Mureinik
  • 297,002
  • 52
  • 306
  • 350
  • It worked. I set up the `user_id` FK from `user` table then `subs_id` from the `subscription` table. Funny how it doesn't have the same effect. I've changed the design to only use `susb_id` but this is good info. Thanks. – Andy Fazulus Sep 14 '21 at 10:46