1

The product_rate_id column in the following table needs to be referenced from another table as foreign key. However, the product_rates TABLE must contain duplicate values for product_rate_id. (Legacy system, don’t ask why)

CREATE TABLE product_rates
(
    row_id             INT  IDENTITY(1,1) NOT NULL,
    product_rate_id    INT NOT NULL,
    START_DATE         DATETIME NOT NULL,
    end_date           DATETIME,
   unit_rate          NUMERIC(18,6)
)

So the question is: can you have a column which is referenced as a foreign key by another table contain duplicate values? Is there any work around for this?

Raju Kumar
  • 1,255
  • 3
  • 21
  • 39
  • 2
    possible duplicate of [Can foreign key NULL and duplicate?](http://stackoverflow.com/questions/7573590/can-foreign-key-null-and-duplicate) – Hart CO Mar 18 '14 at 22:26
  • There's a good answer in the question above. Short answer is a foreign key constraint does not have to be unique. – Hart CO Mar 18 '14 at 22:27
  • How do you create a non unique constraint? – Raju Kumar Mar 18 '14 at 22:28
  • 1
    @GoatCO I'm reading the question differently - I'm reading is as the column the foreign key _refers_ to (`product_rate_id`) is non-unique. Those are two different questions. – D Stanley Mar 18 '14 at 22:31
  • `Col1 int FOREIGN KEY REFERENCES OtherTable(Id_Field)` in this case, `Col1` can have multiple duplicate values, `ID_Field` must have a unique constraint applied. – Hart CO Mar 18 '14 at 22:32
  • The product_rate_id column need to have duplicate rows. I guess Stanley has cleared thing up for me. – Raju Kumar Mar 18 '14 at 22:34
  • @DStanley Ah, un-delete then! – Hart CO Mar 18 '14 at 22:36

1 Answers1

4

can you have a column which is referenced as a foreign key by another table contain duplicate values?

No - a foreign key constraint must reference a unique key.

You can still keep a (non-foreign-key) reference to the product_rate_id value in related tables, but you'll have to decide which related record you want when querying (I'm assuming by using an effective date between start_date and end_date.

D Stanley
  • 149,601
  • 11
  • 178
  • 240
  • Sorry, I'm not agree. You can have a PK like Order.id_order, and a FK to Order_Detail.id_order, having more than one row (order details) associated to a unique id_order value (FK not-unique or duplicated). – Eugenio F. Martinez Pacheco Feb 23 '18 at 11:23
  • @EugenioF.MartinezPacheco That was not the question - the question was can you have a foreign key that references `product_rate_id` which is not unique. – D Stanley Feb 23 '18 at 14:13
  • Ok, I see, you are right @d-stanley :P..... Maybe "product_rate_id" should be replaced by a surrogate key as PK, or choose a composite key for the FK instead... Regards! – Eugenio F. Martinez Pacheco Feb 28 '18 at 09:38