Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

PRIMARY KEY, UNIQUE constraint & UNIQUE INDEX

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
umeshkmrsh

New User


Joined: 21 Sep 2005
Posts: 79
Location: India

PostPosted: Wed Apr 05, 2006 2:05 pm    Post subject: PRIMARY KEY, UNIQUE constraint & UNIQUE INDEX
Reply with quote

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
View user's profile Send private message

perez_a

New User


Joined: 02 Feb 2006
Posts: 25
Location: Italy

PostPosted: Wed Apr 05, 2006 3:17 pm    Post subject: Re: PRIMARY KEY, UNIQUE constraint & UNIQUE INDEX
Reply with quote

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
View user's profile Send private message
umeshkmrsh

New User


Joined: 21 Sep 2005
Posts: 79
Location: India

PostPosted: Wed Apr 05, 2006 3:53 pm    Post subject: Re: PRIMARY KEY, UNIQUE constraint & UNIQUE INDEX
Reply with quote

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
View user's profile Send private message
priyesh.agrawal

Senior Member


Joined: 28 Mar 2005
Posts: 1452
Location: Chicago, IL

PostPosted: Wed Apr 05, 2006 5:18 pm    Post subject: Re: PRIMARY KEY, UNIQUE constraint & UNIQUE INDEX
Reply with quote

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
View user's profile Send private message
umeshkmrsh

New User


Joined: 21 Sep 2005
Posts: 79
Location: India

PostPosted: Wed Apr 05, 2006 6:05 pm    Post subject:
Reply with quote

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
View user's profile Send private message
srini_igsi
Currently Banned

New User


Joined: 09 Dec 2005
Posts: 30
Location: Pune

PostPosted: Tue Aug 01, 2006 4:19 pm    Post subject:
Reply with quote

Hi,

which cataloged table can be queried to know the Primary Key or Unique Key defined over a table?
Back to top
View user's profile Send private message
chiranjeevi_mca

New User


Joined: 19 Feb 2006
Posts: 27

PostPosted: Tue Aug 01, 2006 5:33 pm    Post subject:
Reply with quote

sysibm.sysconstraints is used to view all the constrains of all the tables
Back to top
View user's profile Send private message
srini_igsi
Currently Banned

New User


Joined: 09 Dec 2005
Posts: 30
Location: Pune

PostPosted: Tue Aug 01, 2006 5:47 pm    Post subject:
Reply with quote

It is saying that the Table couldn't be found.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Order BY AND Index Nileshkul DB2 2 Sat Dec 31, 2016 6:33 pm
No new posts Command UNIQUE CONSTRAINT on any tabl... rohanthengal DB2 2 Thu Aug 18, 2016 3:48 pm
No new posts Several errors during building altern... Andi1982 JCL & VSAM 11 Wed Jul 06, 2016 7:39 pm
No new posts Drop building Alternate Index for a p... bhavin.mehta JCL & VSAM 6 Mon Jul 04, 2016 3:47 pm
No new posts SFTP to primary and secondary servers mallik4u JCL & VSAM 4 Tue Jun 07, 2016 12:52 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us