Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

multiple unique columns in a table

Posted on 2011-09-16
8
Medium Priority
?
390 Views
Last Modified: 2012-05-12
can we have multiple unique columns in a table. I was not clear on this concept. Please advise. thanks in advance
0
Comment
Question by:gudii9
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 47

Expert Comment

by:for_yan
ID: 36552810
you can have multiple unique columns in a table - there is nothing that may prevent it
0
 
LVL 47

Accepted Solution

by:
for_yan earned 500 total points
ID: 36552821
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
 
LVL 37

Assisted Solution

by:momi_sabag
momi_sabag earned 500 total points
ID: 36552830
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
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
LVL 21

Assisted Solution

by:Amitkumar Panchal
Amitkumar Panchal earned 500 total points
ID: 36553738
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
 
LVL 46

Assisted Solution

by:Kent Olsen
Kent Olsen earned 500 total points
ID: 36553894
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
 
LVL 7

Author Comment

by:gudii9
ID: 36718886
How to put  unique index on each column
0
 
LVL 47

Expert Comment

by:for_yan
ID: 36718899
you can create index for any column or you can impose unique constraint on each colimn -
thes are different things
0
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 36718913
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

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
In this post we will learn different types of Android Layout and some basics of an Android App.
Via a live example, show how to take different types of Oracle backups using RMAN.
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question