Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
I read table can have only one primary key. Can someone expl

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

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Wed Dec 17, 2008 11:37 am    Post subject: I read table can have only one primary key. Can someone expl
Reply with quote

Hi,

I read table can have only one primary key. i.e., one column i thought.

Below is just a test table. Statement Worked.
Code:
CREATE TABLE hxsull.sentest                   
      (COL1               CHAR(4) not null   
      ,DATETIME           TIMESTAMP not null 
      ,COL2               CHAR(4) not null   
      , PRIMARY KEY (COL1 ,                   
               DATETIME          ,           
               COL2)                         
)                                             


Can some some explain.

Sushanth
Back to top
View user's profile Send private message

Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2285
Location: @my desk

PostPosted: Wed Dec 17, 2008 11:48 am    Post subject:
Reply with quote

Sushanth,

I found this in the same manual. Might be of some help to you.

Key
Quote:
A key is a column or an ordered collection of columns that is identified in the description of a table, an index, or a referential constraint

Unique Key
Quote:
A key that is constrained to have unique values is a unique key
Primary Key
Quote:
A primary key is a special type of unique key and cannot contain null values. A table can have no more than one primary key. Primary keys are optional and can be defined in CREATE TABLE or ALTER TABLE statements.
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Wed Dec 17, 2008 12:08 pm    Post subject:
Reply with quote

Arun,

Quote:
A table can have no more than one primary key.
Now come to think of it. Its absolutely true. There cannot be more than one primary key in a table. But, it never said about how many columns a single primary key can have.

Usually, when we give a single column as a primary key, we create unique index for it. But, when 2 columns are defined as a single primary key. Do we have to create muliple column unique index for this primary key or just 2 separate unique index is enough ?

Sushanth
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Wed Dec 17, 2008 12:26 pm    Post subject:
Reply with quote

Arun,

Just tried it out.
Need Multiple Column Index to insert values in the table.

Can i know what are benefits & drawbacks of doing so, multiple columns in a single primary key.

Sushanth
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2285
Location: @my desk

PostPosted: Wed Dec 17, 2008 12:32 pm    Post subject:
Reply with quote

If you want to know the exact number of columns allowed in an index, have a look at this.
Quote:
column-name,…
Specifies the columns of the index key.

Each column-name must identify a column of the table. Do not specify more than 64 columns, the same column more than once, or a LOB or DECFLOAT column (or a column with a distinct type that is based on a LOB or DECFLOAT data type). Do not specify a BINARY or VARBINARY column (or a column with a distinct type that is based on a BINARY or VARBINARY data type) or a row change timestamp column for column-name when the PARTITION BY RANGE clause is also specified. Do not qualify column-name
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2285
Location: @my desk

PostPosted: Wed Dec 17, 2008 12:36 pm    Post subject:
Reply with quote

I think it's determined based on your data requirements. It's more likely that you need to create unique keys consisting of more than one column rather than a single column.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Wed Dec 17, 2008 8:20 pm    Post subject:
Reply with quote

Hello,

Quote:
Can i know what are benefits & drawbacks of doing so, multiple columns in a single primary key.
I believe you are approaching this in the wrong order.

First, the requirement must be defined.

Then, the underlying data structures can be defined.

Many tables have a multi-column primary key (when an arbitrary system generated value is not used for the primary key).
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Thu Dec 18, 2008 9:48 am    Post subject:
Reply with quote

Hi Dick,

I don't have a requirement currently for a multi-column primary key. I came across a table which was having a primary key like this. So, i got little bit confused. Because i read, table can have only one primary key, i never thought columns can be combined to make a single primary key. That's why, i asked.

Thank You,
Sushanth Bobby
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Thu Dec 18, 2008 9:57 am    Post subject:
Reply with quote

You're welcome icon_smile.gif

Quote:
i never thought columns can be combined to make a single primary key
This can be done for secondary or alternate keys also.

If you look at the definitions of tables currently in use on your system, there may be many new things discovered icon_wink.gif
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 Update table without cursor Ron Klop DB2 3 Wed Oct 11, 2017 1:06 pm
No new posts Table(Unicode(Graphic) table) loading... muralikrishnan_new DB2 0 Thu Oct 05, 2017 5:10 pm
No new posts Insert a Row_number into table useit DB2 2 Tue Sep 19, 2017 1:07 pm
No new posts how to see when the last read access ... Mike 1304 DB2 1 Tue Sep 12, 2017 7:52 pm
No new posts PC (UTF-8) -> z/OS (EBCDIC) -> ... prino All Other Mainframe Topics 4 Fri Sep 01, 2017 1:47 am

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us