This works since version 7.0 - 1992/1993...


Create table is a DEVELOPER command, not a DBA task ;)


I have nothing scheduled in Madrid, but I will be coming to Finland, London, Dublin, Munich, Paris, Zurich in 2006

if they are all "valid since", which one is "THE VALID" one??

If this is a table maintaining history, perhaps you need a "current address" object and "historical addresses" (eg: data model needs to be looked at)

You are missing a table in the schema.

you are missing a table then - the parent table of address, address is the child table of something else and it is missing.


seems whacky - cannot imagine how the software would pick the "most appropriate" but basically, you are missing a table.

.... Clearly we should guarantee that there is at least one address (the validity
would not matter) for each client using this address. ...



You can use an on commit refresh materialzed view with a check constraint if you like.

Or you can use a trigger, trigger would maintain a count in the "site" table. It would effectively serialize on the site row (avoids multi-user concurrency issues).

You would not be counting rows in a trigger, you would be maintaining a rolled up count that the database would serialize on and verify for us.


It would not be enforcing a constraint across rows- the constraint would boil down to a check constraint on A row.

Something like this:

create table p
( x int primary key,
/* other stuff */
addr_cnt number default 0
not null
constraint addr_cnt_gt_zero
check ( addr_cnt>0 )
deferrable initially deferred
)
/

create table c
( x,
y int,
data varchar2(10),
constraint c_fk foreign key (x) references p,
constraint c_pk primary key(x,y)
)
/

create or replace trigger c_trigger
after insert or update or delete on c for each row
begin
if (inserting or ( updating and :new.x <> :old.x ) )
then
update p set addr_cnt = addr_cnt+1 where x = :new.x;
end if;
if (deleting or (updating and :new.x <> :old.x ) )
then
update p set addr_cnt = addr_cnt-1 where x = :old.x;
end if;
end;
/

you might even look into materialized views with check constraints:
</code> https://www.oracle.com/technetwork/issue-archive/2005/05-mar/o25asktom-089621.html <code>

as well.

I've also said this countless times. Apps like that are "not quite fully baked" when they are shipped. They are "not done". They are 80% of the way there - the rest is called "implementation" (that is why implementing these packaged apps takes, well, longer than a weekend).

When you are writing your application for your company, you will be "done", you will have a 100% implemented application. You *know* what is to happen to everything.


And do I agree 100% with the way everything is implemented in the applications (ours, theirs, yours, anyones?) of course not.

"generic" = "loss of data integrity" & "bad performance" & "inability for mere mortals to query" & "other bad stuff"

(C programers will tell you what && means..)

tell you what, you share with me the 'trigger' that does this and I'll tell you why it "won't work"

It won't be done via declarative constraints, a child has a parent, you would need to make all of the "possible parents" children of some "master parent" and the "generic details" the child of this master parent and then figure out how to provide for mutual exclusion amongst the "possible parents" and --- well, you aren't doing a relational database anymore.

that'll never work.

a) where is the trigger on the child tables (for completeness)

b) describe what happens when:

time 1: I insert a row into master1 with pk = 1
time 2: you run your trigger (which doesn't see my insert)
time 3: I commit
time 4: you commit


where is your LOCK TABLE on all of the tables? Even Oracle would use a LOCK table command for RI enforcement if you don't index the foreign keys.

now we start to wonder "why" since they have to be joined very differently? why have it "generic" - if I specify a tablename or specify a specific where clause.

I'd rather specify a tablename.

Take your generic thing up a notch to 10 parents for example, it gets rather cumbersome rather quickly.

it is a NORMAL function based index. the function is "CASE"

well, what happens when you export/import this data?

Seems to be a constraint that only makes sense in CERTAIN conditions. Therefore, I would not suggest a constraint or trigger at all - but rather a TRANSACTIONAL API written in plsql that applications call (eg: no one ever inserts into this table except for well formed business transactions that do the right thing)


So, write a procedure....

how would this be useful? can you give a strawman example of what use a constant in a foreign key constraint would be?

well, the design is "wrong", so yes you would have to propagate that down. Sorry, not everything should be "fixed" for every bad design decision.

the 10 and 11 need to be attributes down into the client table, period. I would not ever want this "constant" feature to even be considered for the reason given.

read the other comments - it could have been done more optimally - a simple

select count(*) from t where fk = :bv and rownum = 1;

would have been better in my opinion.