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

[monkeyboy]

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

[johnb...@sbcglobal.net]

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?

[monkeyboy]

Thanks for the quick response.

Agreed triggers are bad. I’ve managed to avoid them until now and
don’t really want to start.

Indeed there is an ERD for the application. The id for the main/parent
table ‘foo’ is generated from a sequence, ‘sq_foo_id’.

This is a system that is currently in production where all database
access (inserts, updates as well as selects) is done through stored
procedures. My current task is to move away from these and implement
some Java OR mapping instead using Hibernate.

I was under the impression that it was possible to tell Hibernate that
the value in a column is generated by calling some database
functionality, i.e. sequence. But it appears that by telling Hibernate
a column is generated it assumes that this is done in the background
and then performs a select on the table once the insert is completed
to get the values that have been created for you. Therefore, I thought
that if I could get the database to default a value, or have the evil
trigger create it then I could work around the OR mapping tool.

I guess as the ‘weird_id’ can be produced by concatenating a function
on the insertion date, with some text and a function on the foreign
key. And in the parent table we have the created date/time I could
create a view over this table to expose the value I want. Inserts
should still work as the view will only be across one table (and I
wont need to create any insert triggers…)

[roelof.s...@gmail.com]

Not all triggers are bad :) triggers that do data integrity checks are
ok to code.
meaning triggers that do not change data!!!!

The problem with triggers is if they start doing things other then
data checks.
ei logic like this update record A in table y if state changes on
record B in table x.
Then you have an nice opaque system, which leads you to have gray hair
early in life
sooner then you which for.

Thanks Roelof

You should also read this blog:

http://thehelsinkideclaration.blogspot.com/2009/03/helsinki-declaration-observation-1.html

His book about "Applied Mathematics for Database Professionals" is
also a very good read.

[monkeyboy]

> http://thehelsinkideclaration.blogspot.com/2009/03/helsinki-declarati...

>
> His book about "Applied Mathematics for Database Professionals" is
> also a very good read.

OK, so what I've done now is to create a trigger like this:

CREATE TRIGGER tr_insert_bar
BEFORE INSERT
ON tb_bar
FOR EACH ROW
BEGIN
:new.weird_id := pk_code_package.sf_weird_id_generator(:new.bar_id);
END tr_insert_bar;
/

which seems to be doing the trick for the moment...