| What kind of primary key for a table which contains constants? [message #36140] |
Wed, 07 November 2001 08:06  |
StephFromFrance
Messages: 1
Registered: November 2001
|
Junior Member |
|
|
Hello,
I'm creating two tables which contain static data (which will be only updated directly in the database).
I'm hesitating between creating a normal primary key number(38), or a 3 letter code (char(3)), which will be easier to refer into the code.
And I also do not need a sequence to insert new rows.
What is the most efficient and what takes less place?
Any inconvenience to use codes instead of 'real' primary keys generated by sequences?
Thanks
S
----------------------------------------------------------------------
|
|
|
|
| Re: What kind of primary key for a table which contains constants? [message #36146 is a reply to message #36140] |
Wed, 07 November 2001 13:23  |
andrew again
Messages: 2577
Registered: March 2000
|
Senior Member |
|
|
assigning your own PK's is fine as long as you are sure that the codes you choose will never change (you should never update a PK value - especially if child records exist). There can be an advantave to doing what you suggest because you don't constantly have to refer back to the master table to know what the FK column means as the code you choose becomes meaningful to developers (and users who maybe need to do ad-hoc queries. You will see that Oracle often uses codes on tables, enen if there is no parent table containing the descriptions. e.g. user_constraints.constraint_type. If you don't have a lookup table, you can use "decode" when you need the meaning.
select decode (CONSTRAINT_TYPE, 'C', 'Check', 'P', 'Primary Key', 'R', 'Referential Integrity',
'U', 'Unique Key', 'V', 'Check Option on a view') from user_constraints where rownum < 5;
Don't worry too much about space considerations, these are normally not an issue.
----------------------------------------------------------------------
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
