I started reading this thread to better understand the idenfiying/non-identifying relationships.
It seemed to me that the identifying/non-identifying relationships boils down to, do we add a surogate key or not?


Now r937, why do you say it is redundant? Maybe I didn't understand it right.
Asuming that column1 and column2 form togheter the natural key, I thought that by adding a surrogate key, and by adding a constraint UNIQUE (column1, column2) , there shouldn't be any problem, and it's better than the table without the constraint just with the surrogate key.
This way you avoid having doubles in your table, and you have a surogate key. There is a small penalty for inserts and deletes, but it's not that huge.


Thanks

the example that i said was redundant is this --

suppose a table with PK of (foo,bar)

then declaring a unique key of (foo,bar) would be redundant

I agree.

what about this case

Id (surrogate key) (PK),
foo,
bar
+ CONSTRAINT UNIQUE(foo,bar)

The Id column would be unique by itself and foo and bar together would be unique.

In ER modelling terms a relationship is called an identifying one if it corresponds to a foreign key that is some part of a primary key. However, the distinction between identifying and non-identifying is of questionable value as soon as you have more than one candidate key.

In your example, assuming both keys are enforced by uniqueness constraints, it really doesn't matter which candidate key ("id" or "foo,bar") you call the "primary" one. The semantics and the end results are exactly the same in either case. Yet ER modelling insists on describing the relationship differently depending on which key you choose to call "primary".

Decide what keys you need in your database to model the problem accurately. Don't worry too much about the identifying vs non-identifying concept.