REF with user defined types - "datatype specification not allowed"

[JSims281]

I'm new to oracle/sql, and I'm trying to set up a database which has a 'Customer' table, an 'Account' table and a 'CustomerAccount' table (which links the two).

I've defined types for the account numbers and customer ID numbers and I'm wondering how to create the customeraccount table.

This is what I'm looking at just now:


CREATE TYPE customeraccountType AS OBJECT(
custID varchar2(4),
accnum varchar2(10));
/
CREATE TYPE custIDType AS OBJECT (custID varchar2(4));
/
CREATE TYPE accnumType AS OBJECT (accNum varchar2(10));
/
CREATE TABLE customerAccountTable of CustomerAccountType(
AccNum ref AccNumType,
CustID ref CustIDType
);

But this doesn't work, and I get the "ORA-02330: datatype specification not allowed" error.

I can make the table fine by defining the accnum and custid as varchars when I create the table, but really I want to be using UDTs for them, to help with selecting data later on in the implementation. I've also tried putting the REFs in the CustomerAccountType definition but couldn't get this to work either.

Any pearls of wisdom would be really appreciated!
(I'm using Oracle 10g)

JS

 

[JSims281]

EDIT: I want to have the CustID and AccNum as foreign keys in the customeraccount table, which gives me issues as a "column of datatype REF cannot be unique or a primary key".

Or I just taking the wrong end of the stick here, then beating myself with it?

 

[SantaMufasa]

JS,

My initial impression is that you are working much harder than you need to insofar as Oracle is concerned. To achieve what you describe, I would simply do the following (in Oracle):

CREATE TABLE customer
      (custID NUMBER PRIMARY KEY
      ,<other columns here>);

CREATE TABLE account
      (accnum NUMBER PRIMARY KEY
      ,<other columns here>);

CREATE TABLE customeraccount
      (Accnum references account(accnum)
      ,CustID references customer(custID)
      );

Let us know what functionality is missing from the above statements.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]