"ORA-00984: column not allowed here" Creating table

I'm sorry if this has been asked before, but my searching has only thrown up all kinds of error, which don't quite match what I'm doing.

I am creating a table bar with multiple columns, where foo_id is a foreign key reference to the table foo,

CREATE TABLE bar
(

  foo_id NUMBER (14,0) NOT NULL,
  weird_id VARCHAR2(32) ,

• other columns FOREIGN KEY(foo_id) REFERENCES foo );

My weird id is actually a function of foo_id created with a stored function:

FUNCTION generate_weird_id ( p_normal_id IN NUMBER ) RETURN VARCHAR IS
  v_weird_id VARCHAR(32);
BEGIN
  SELECT TO_CHAR(SYSDATE, 'YYDDD') || lpad(p_normal_id, 8, 0)   FROM DUAL;
  RETURN v_weird_id;
END generate_weird_id;

So I created the table as:

CREATE TABLE bar
(

  foo_id NUMBER (14,0) NOT NULL,
  weird_id VARCHAR2(32) DEFAULT generate_weird_id(foo_id),

• other columns FOREIGN KEY(foo_id) REFERENCES foo );

I can understand that I'm not supposed to use the one column as a parameter for the input to a function defining the default value for another, but how else can I achieve this?

Do I need to have an update trigger?

Thanks Received on Mon Apr 06 2009 - 06:40:36 CDT