Invalid Field Names?

[dunc0029]

To make a long story short, we may be moving to an Oracle backend from a Visual FoxPro database. We are actually in the process of rewriting an old FoxPro 2.6 application in VFP 8.0 for the front end. In any event, we are making some data changes at the same time. Currently, the keys (foreign and primary) use an "_Key" naming convention. My manager says she heard somethinb about Oracle not liking field names with the letter "k", but I'm thinking that would be pretty weird, and maybe it's just "KEY" that can't be used in a field name? Is there any truth to that? We're just considering renaming all key fields now in preparation for the Oracle migration. Thanks!!!

 

[sybaseguru]

dunc0029,

Firslty like any other RDBMS you need to be aware of keywords, reserverd words and namespaces in Oracle. The following link will help you
[COLOR=blue}
[code]

http://www.csis.gvsu.edu/GeneralInfo/Oracle/appdev.920/a97269/pc_abres.htm#631174

[/code]
[/color]
Obviously you are also aware of the same limitations within ANSI SQL as well. With regard to 'key' issue that you brought up any suffix should be fine and you can use column names starting with letter "k" or even called "key" (see below, I used it as a column name in table, a bit confusing so not recommended). For example, emp_pkey can be the primary key constraint for table emp etc. I will give an example:

  1  CREATE TABLE kword (
  2  key        NUMBER NOT NULL,
  3  key2       VARCHAR2(30) NULL,
  4  CONSTRAINT kword_pkey PRIMARY KEY (key)
  5* )
scott@MYDB.WORLD> /

Table created.


CREATE TABLE dept (
     deptno NUMBER PRIMARY KEY,
     dname VARCHAR2 (30)
     );
CREATE TABLE emp (
     empno NUMBER,
     ename VARCHAR2 (30),
     deptno NUMBER REFERENCES (dept),
     CONSTRAINT emp_pkey PRIMARY KEY (empno) ,
     CONSTRAINT emp_fkey FOREIGN KEY (deptno)
     REFERENCES (dept.deptno) );

You can see here that the primary key and foreign keys are defined with suffix pkey and fkey.

Again it is all to standards that you will be using. If you are migrating I suggest that you look at websites which have relevant Oracle programming standards and follow those. For your references I recommend

http://tahiti.oracle.com/

website. Also make sure taht you are consistent everywhere to make life easier for you and your developers.

good luck and hope this helps

 

[dunc0029]

Thanks a ton! Very helpful. I just wanted to check because my manager had heard something, even though it sounded very fishy to me.

 

[sybaseguru]

No sweat, any problem come back to this forym. Always glad to help and good luck.