Why are UNIQUE Constraints needed in database ?
Can you provide any examples ?
Primary Key is UNIQUE by default... Understandable as they are referred in other tables as Foreign keys... relation is needed to connect them for rdbms platform...
but why would one refer to other columns as UNIQUE, what is benefit of doing so ?)
Constraints should be used as much as possible to ensure that the database complies with expectations. In this particular case, unique constraints are most useful in ensuring data quality.
A unique constraint MIGHT by useful on an email address column, for instance, which would require that no two rows have the same email address - while it would not be a PK, and typically would be allowed to change.
Any time where you have an expectation of uniqueness, and the value is not already constrained by a PK or similar, than adding a unique constraint can ensure that your assumptions are always preserved.
In general, constraints can also be used by the optimizer.
The second article in Celko's series on constraints is specifically about unique constraints.
emailID can be unique .
difference between uniqueID and Primary key is unique support a single null in the entire column but PK wont.
When something is a primary key, I don't expect it to change - it should be static, since it's used for linking to other tables in your database. If your "primary key" will be changing (say, a username), then it ought to be an additional field in your table, and the primary key should instead be some kind of incrementing ID.
However, you still can't have two users with the same username - in that case, a unique constraint is warranted.
Most of the times while we design the database we keep our primary keys as Identity Field. Even though it makes sense to make it as identity field it might not solve the issue of achieving uniqueness. To make sure that the row is unique we put a unique constraint on the column (as Andrey specified a UserNane). This is what MSDN say:
You can use UNIQUE constraints to make sure that no duplicate values are entered in specific columns that do not participate in a primary key. Although both a UNIQUE constraint and a PRIMARY KEY constraint enforce uniqueness, use a UNIQUE constraint instead of a PRIMARY KEY constraint when you want to enforce the uniqueness of a column, or combination of columns, that is not the primary key.
HTH
There are a couple of differences between a PK and a UQ. A PK can have no NULL values where a UQ can have 1 null value (Oracle allows multiple NULL values). You can only have 1 PK per table but you can have multiple UQ per table A PK by default is clustered (but doesn't need to be)
If the data is required to be unique, then you need a unique constraint. Otherwise you will get poor data. A PK must be unique but that doesn't mean other data is not required to be unique as well. Perhaps each record must have a unique datetime, this is unlikely to be the PK, but the uniqueness must be enforced somehow.
In particular if you use a surrogate key for the PK (which I highly recommend) , then you want to ensure the natural key fields are part of a unique contraint to avoid duplication of data.
Same thing for lookup type data. Suppose you have a list of professional specialties for doctors that your users can choose from when they enter data and suppose they are also allowed to add to this list if they need to. A unique constraint will prevent Oncologist from being entered multiple times which will make is easier when you want ot actually find the number of people who are Oncologists.