oracle table creation

Question

The following code gives me ERROR at line 3: ORA-00907: missing right parenthesis:

CREATE TABLE ORGANISATION(
    ORG_REF VARCHAR(5),
    POSTCODE VARCHAR(10) FOREIGN KEY,
    TELEPHONE NUMBER FOREIGN KEY, 
    DESCRIPTION VARCHAR(30),
 AGENCY_ID VARCHAR(5));

Line 3 code is very annoying because looking at the line there are no spelling mistakes and everything is in the right place.

line 3 is POSTCODE VARCHAR(10) FOREIGN KEY, so what is wrong with this line of code — john, Apr 29 '12 at 16:29
Have you ever looked at anything [that shows you how to create a foreign key](http://www.techonthenet.com/oracle/foreign_keys/foreign_keys.php)? — Ben, Apr 29 '12 at 16:33
You might like to store telephone numbers as varchar's rather than numbers, as you often need to preserve non-digit characters such as +()- and you generally perform character operations on them rather than arithmetical operations. — David Aldridge, Apr 30 '12 at 08:15

Marc · Answer 1 · 2012-04-29T16:58:45.487

5

That's not how you define a foreign key. A foreign key must know how to find it's partner.

Read here: http://www.techonthenet.com/oracle/foreign_keys/foreign_keys.php

Foreign key definition goes something like this:

CREATE TABLE ORGANISATION(
  ORG_REF VARCHAR(5),
  POSTCODE VARCHAR(10), --THIS WILL BE FOREIGN KEY
  TELEPHONE NUMBER,     --2nd FOREIGN KEY
  DESCRIPTION VARCHAR(30),
  AGENCY_ID VARCHAR(5),
     FOREIGN KEY FK_POSTCODE
     REFERENCES other_table (post_code),
     FOREIGN KEY FK_TELEPHONE
     REFERENCES other_table2 (phone)
);

UPDATE:

Additional Recommended Reading: http://mattgemmell.com/2008/12/08/what-have-you-tried/

edited Apr 29 '12 at 16:58

answered Apr 29 '12 at 16:33

Marc

11,403
2
35
45

5

@john, you would read the documentation, try to learn from it, and then do something nearly identical to what I've written above. And then if it worked, you'd be sure to mark this answer as a correct solution. – Marc Apr 29 '12 at 16:42
my postcode and telephone foreign key will be linked to the agency entity which also has POSTCODE AND TELEPHONE FOREIGN KEY. So it should be defined as FOREIGN KEY FK_POSTCODE REFERENCES AGENCY(POSTCODE), FOREIGN KEY FK_TELEPHONE REFERENCES AGENCY(PHONE)); – john Apr 29 '12 at 16:46
my postcode and telephone foreign key will be linked to the agency entity which also has POSTCODE AND TELEPHONE FOREIGN KEY. So it should be defined as FOREIGN KEY FK_POSTCODE REFERENCES AGENCY(POSTCODE), FOREIGN KEY FK_TELEPHONE REFERENCES AGENCY(PHONE)); i just want to understand this foreign key with this example – john Apr 29 '12 at 16:54
4

@john, You've made no effort to understand this on your own, which is a fundamental tenet of why people become willing to help you on Stackoverflow. I'm updating my answer with one additional reading recommendation in response to your original question. – Marc Apr 29 '12 at 16:58

score 4 · Answer 2 · edited May 23 '17 at 11:46

Where to start?

You should be using varchar2 not varchar. Although they're currently identical the future behaviour of varchar is not guaranteed
Telephone number as a numeric field? A lot of phone numbers start with a 0. You're losing this. If you ever want to display it nicely you have to do some funky string manipulation on exit.
If your IDs are numbers then you should store them as a number.
There is rarely a situation where a table should not have a primary key.

A foreign key is designed to enforce referential integrity in the database. There should therefore be one or two more tables in this schema as a minimum.

A typical situation might be like this, which assumes that the same postcode,phone combination exists in agency.

CREATE TABLE ORGANISATION(
    ORG_REF VARCHAR2(5),
    POSTCODE VARCHAR2(10) ,
    TELEPHONE VARCHAR2(50), 
    DESCRIPTION VARCHAR(30),
    AGENCY_ID VARCHAR(5),
    CONSTRAINT PK_ORGANISATION PRIMARY KEY ( org_ref ),
    CONSTRAINT FX_ORGANISATION FOREIGN KEY
       REFERENCES SOME_OTHER_TABLE(POSTCODE,PHONE)
   );

If it were just a single column and not 2 you could reference it inline, something like the following:

create table organisation (
     org_ref number(16) not null
   , phone varchar2(5) not null constraint fk_organisation
        references agency ( phone )
   , constraint pk_organisation primary key ( org_ref )
   );

However, I doubt very much that this'll work. A foreign key must reference a unique constraint. So, judging by your comments you must have a table agency with a unique constraint or primary key on phone, postcode.

I suspect your data-model is flawed; it sounds as though organisation inherits from agency.

I would remove the phone and postcode from agency and just do a join to get that information, if you're currently looking at the agency table:

select a.*, o.postcode, o.phone
  from agency a
  join organisation o
    on a.agency_id = o.agency_id
 where a.id = 12345

oracle table creation

3 Answers3

Further reading: