|
View previous topic :: View next topic
|
| Author |
Message |
umeshkmrsh
New User
Joined: 21 Sep 2005
Posts: 79
Location: India
|
|
|
|
PRIMARY KEY, UNIQUE constraint & UNIQUE INDEX
I have doubts regarding above three constraints in DB2, you all are requested to help me in clarifying my doubt. I am describing below my understanding of the above constraints. If you feel I am wrong please correct me giving proper reason.
NOTE: Please reply only if you are 100% sure. Also I am taking about DB2 and not about any other relational database).
Primary Key: Cannot have NULL values and the values must be unique.
Primary Key = Unique Constraint + NOT NULL constraint.
UNIQUE: Values in column should be unique, but only one NULL value is allowed. We cannot have multiple NULL values in a column having UNIQUE constraint.
UNIQUE INDEX: Values in the column must be unique, but with exception ? that we can have multiple NULL values. I doubt it?
But it's sure that all these three are different. |
|
| Back to top |
|
 |
perez_a
New User
Joined: 02 Feb 2006
Posts: 25
Location: Italy
|
|
|
|
Hi umeshkmrsh ,
when u define an unique index, it can assume null value only 1 time.
Have a nice day perez_a |
|
| Back to top |
|
|
umeshkmrsh
New User
Joined: 21 Sep 2005
Posts: 79
Location: India
|
|
|
|
Hi perez_a,
| Quote: |
when u define an unique index, it can assume null value only 1 time.
Have a nice day perez_a |
Then what's the difference between UNIQUE constraint and UNIQUE INDEX?? I know that UNIQUE constraint is internally handled using UNIQUE INDEX but they are different as far as NULL values are concerned. |
|
| Back to top |
|
|
priyesh.agrawal
Senior Member
Joined: 28 Mar 2005
Posts: 1448
Location: Chicago, IL
|
|
|
|
| Quote: |
| NOTE: Please reply only if you are 100% sure. |
Then some body- has written manuals only- would have to come answering you. So why not you make a search yourself.
Pls try to avoid such remarks..its a discussion forum...not a Pay n Ask Shop
| Quote: |
| what's the difference between UNIQUE constraint and UNIQUE INDEX?? |
Check this link...
Regards,
Priyesh. |
|
| Back to top |
|
|
umeshkmrsh
New User
Joined: 21 Sep 2005
Posts: 79
Location: India
|
|
|
|
Actually I was reading a manula and I got confused.
| Quote: |
| Although a unique, system-required index is used to enforce a unique constraint, there is a distinction between defining a unique constraint and creating a unique index. Even though both enforce uniqueness, a unique index allows nullable columns and generally cannot be used in a referential constraint (the value "NULL" means a field's value is undefined and distinct from any other value, including other NULL values) while a unique constraint does not. |
?[/quote] |
|
| Back to top |
|
|
srini_igsi
Currently Banned
New User
Joined: 09 Dec 2005
Posts: 30
Location: Pune
|
|
|
|
Hi,
which cataloged table can be queried to know the Primary Key or Unique Key defined over a table? |
|
| Back to top |
|
|
chiranjeevi_mca
New User
Joined: 19 Feb 2006
Posts: 27
|
|
|
|
| sysibm.sysconstraints is used to view all the constrains of all the tables |
|
| Back to top |
|
|
srini_igsi
Currently Banned
New User
Joined: 09 Dec 2005
Posts: 30
Location: Pune
|
|
|
|
| It is saying that the Table couldn't be found. |
|
| Back to top |
|
|
|
|
