|
View previous topic :: View next topic
|
| Author |
Message |
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
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 |
|
 |
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2481
Location: @my desk
|
|
|
|
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 |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
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 |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
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 |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2481
Location: @my desk
|
|
|
|
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 |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2481
Location: @my desk
|
|
|
|
| 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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
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 |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
You're welcome 
| 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  |
|
| Back to top |
|
|
|
|
