Menu
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 ?)
625
You can create a unique constraint in SQL Server by using SQL Server Management Studio or Transact-SQL to ensure no duplicate values are entered in specific columns that do not participate in a primary key. Creating a unique constraint automatically creates a corresponding unique index.
SQL UNIQUE Constraint Basics A UNIQUE constraint dictates that a column must not store duplicate values. Let's see an example using the Employees table. Each employee must have a unique number; hence, the EmployeeNumber column implements the UNIQUE constraint.
In this example a given column (the column name ) was made unique by adding the clause UNIQUE at the end of the definition column ( name VARCHAR(100) UNIQUE ). This new table (in our example: product ) will contain the column (in our example: name ) that stores unique values in rows.
You can create 999 Unique constraints per table.
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.
160
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
