Duplicate columns values within a table

Question

Hi,

My scenario is I have a table with columnxs A, B, C and D. A is the PK and B and C are both foreign keys.

I intend to add an exta column to this table E, the side effect of this within the application is it will cause a row to be inserted after which there will be duplicate values within columns B and C.

Does this break any referenntial rules? I am assuming so since if any joins are done to this table on these foreign keys then they will no longer return a unique row.

Thanks,

Answer 1

If the relation is one to many, you're not breaking a referential rule by having multiple rows referring to a single foreign row.

Adding an extra column in this way might break existing applications. A query like this, that used to return one row, might now return many rows:

 select *
 from table
 where b=1 and c=2

Answer 2

If you have extensively used correlated subqueries in your application, you still might not be safe as the new rules might mean a subquery which used to return only one record now might under some circunmstances return two or more and then it will break. Just because the table allows one to many records doesn't mean all the correlated subqueries haven't been written to filter to only one record (since they can't work any other way) so they might be affected by the change. This (as well as performance) is one reason why I do not ever use correlated subqueries.