multiple unique columns in a table

can we have multiple unique columns in a table. I was not clear on this concept. Please advise. thanks in advance
LVL 7
gudii9Asked:
Who is Participating?
 
for_yanCommented:
Let's say you have the table of patients of the hospital - they may have their own id for each patient , but they may have a column for insusrance id and those
may be also be unique - certainly no problem with that
0
 
for_yanCommented:
you can have multiple unique columns in a table - there is nothing that may prevent it
0
 
momi_sabagCommented:
you can have as many unique constraint you want on a table
a unique constraint verify that a column or set of columns have unique values in every row in a table
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Amitkumar PSr. ConsultantCommented:
Yes, You can have unique constraint on the all fields of the table. Only thing is that if the column having unique constraint must contains a unique value in that field across the table. However, the field contains a null (no value) value in it, so multiple null values are allowed in the field having a  unique constraint.
0
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi gudi,

The easiest way to enforce multiple unique columns in a table is to put a unique index on each of the columns.  

In fact, if you have a significant amount of data in the table it will be almost imperative to have these columns indexed as every insert or update will need to verify that the new data is unique within the column.  Without indexes on these column a full table scan will be required just to insert a new row!


Good Luck,
Kent
0
 
gudii9Author Commented:
How to put  unique index on each column
0
 
for_yanCommented:
you can create index for any column or you can impose unique constraint on each colimn -
thes are different things
0
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi gudi,

You'll need to add the index or constraint for each column that must be unique.

CREATE UNIQUE INDEX idx0001 ON mytable (column1);
CREATE UNIQUE INDEX idx0002 ON mytable (column2);
etc....



Kent
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.