Home » SQL & PL/SQL » SQL & PL/SQL » What kind of primary key for a table which contains constants?
What kind of primary key for a table which contains constants? [message #36140] Wed, 07 November 2001 08:06 Go to next message
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 Go to previous message
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.

----------------------------------------------------------------------
Previous Topic: Please suggest
Next Topic: Win200 and Oracle
Goto Forum:
  


Current Time: Fri May 27 23:03:59 CDT 2022