Re: "ORA-00984: column not allowed here" Creating table
Date: Mon, 6 Apr 2009 05:31:48 -0700 (PDT)
Message-ID: <f272acae-deec-47d9-9a22-e693c80bab3b_at_r37g2000yqn.googlegroups.com>
On Apr 6, 7:40 am, monkeyboy <goo..._at_vellosa.co.uk> wrote:
> Hi All,
>
> 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
Well Tom Kyte says ( and I believe him ) that triggers are evil and should be avoided where at all possible so the answer to "Do I need to have ... " is obviously no.
You need to have an application that is designed so that parent table ( the one referenced ) is populated with rows before rows in the child table(s) are created.
In oracle often one has a setup where both sets of id's in the parent/ child tables are generated from sequences ( some people like one sequence per table some people like shared sequences ) ... with alternate sets of other keys and indexes as the application design requires. Many to one relationships use embedded foreign keys typically in the many side.
I guess the first question I should have asked is have you done a complete ERD model for the application? It kinda sounds like you are busy coding away perhaps before the design has been validated? Received on Mon Apr 06 2009 - 07:31:48 CDT