Afternoon SanTa

>> The main question is: is there any
>> reason for both of them, and if not, then should we always
>> use unique constraint instead of unique index?

The way I see it is this:

* A unique index prevents me from entering two rows with the same data
in all the column(s) making up the index;

* A unique constraint is a rule to enforce a business rule or decision.
The constraint will use a unique index to enforce the constraint.

* You simply cannot have referential integrity between two tables unless
you have a primary key or a unique constraint on the parent table.

So that's it in a nutshell. I can add a unique index to any table I
like, provided it fits with my database design, but I need not use those
indexed columns for referential integrity.

Example:

CREATE TABLE DEPARTMENT(
ID INTEGER,
NAME VARCHAR(100),
...
);

CREATE UNIQUE INDEX DEPARTMENT_NAME
ON DEPARTMENT (NAME);

My database design disallows the duplication of department names, but I
don't need a unique constraint as I don't need to have any child table
of the department table, reference the NAME column. They would most
likely reference the ID column instead.

If I choose to, I can create a unique constraint on the ID (rather than
a primary key) column and have other tables reference it, as follows:

ALTER TABLE DEPARTMENT
ADD CONSTRAINT DEPARTMENT_UNQ_ID
UNIQUE (ID);

It's not the smarted design decision in the world because it allows NULL
id's to be stored in the table, a primary key prevents this. In
addition, multiple NULLs can be stored!

Now, I can have referential integrity:

CREATE TABLE EMPLOYEE(
ID INTEGER,
DEPT_CODE INTEGER,
...
);

ALTER TABLE EMPLOYEE
ADD CONSTRAINT EMP_DEPT_FK
FOREIGN KEY
REFERENCES DEPARTMENT (ID);

Cheers,
Norman.


Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else. We have checked this email and its attachments for viruses. But you should still check any attachment before opening it. We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes. If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.

We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.

If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk