CREATE TABLE table_name (
   id    INTEGER  PRIMARY KEY,
   col2  CHARACTER VARYING(20),
   col3  INTEGER,
   ...
   CONSTRAINT col3_fk FOREIGN KEY(col3)
      REFERENCES other_table(key_col) ON DELETE CASCADE,
   ... )
 

or if the foreign key is a single column it could be as easy as this

CREATE TABLE table_name (
   id    INTEGER  PRIMARY KEY,
   col2  CHARACTER VARYING(20),
   col3  INTEGER REFERENCES other_table(column_name),
   ... )
 

At least one wouldn't any more have to think about the "Sql Integrity"-alternative,  which now is always forgotten.
The disadvantage is that making changes afterwards is perhaps a bit more tough, but isn't this the right way to go.......

Regards RogerW

Sounds like wish to me. Might be a nice enhancement, but it should remain optional.

Hi Theo,

To me it was a real surprise when a youngster told me that we are not using real relational databases (using Unface database-scripts). Having investigated it, yes I started to somehow agree.
So, now I'm suggesting the native way to be the default way and the trigger-creation being the optional way. At least if we want Uniface to remain the main tool for database table creation :-).

I understand that it would need some work for you to make this change into the database driver, and as the old way does work there willl probably not be much done to this. However if you are at the same line as me, believing that you use real relational databases although you aren't, I just wanted to give this some attention.

If you start to touch your database drivers, think about the server-side generated surrogate keys at the same time.

Kind Regards RogerW.     

Hi RogerW.

unfortunately, uniface provides means like directing entities to other databases etc.
which are used by a couple of applications I know.

Forcing the db-constraints into the simple "create table" sql script will be contraproductive in this case.

What is wrong having means to generate 2 sql scripts; one for create table, one for create constraints?

Success, Uli

Hi Uli,

There is nothing wrong with having to generate two sql-scripts (knock on wood). But if you use Uniface for table generation, you expect it to build the database as almost all other tools expect it to be done. Many tools, eg. UML modelling tools etc. do need the real foreign keys.
The same problem exists with server-side generated surrogate-keys, which nowadays seem to be the "default" way of building a relational database. Uniface doesn't support server-side generated surrogate keys. 

So, this is very much an integration matter, not just an Uniface matter.

Regards RogerW.

We ditched using triggers for RI on SQLServer a few years back. We now generate RI scripts for Oracle which contains foreign keys and we amend them to run for SQLServer. The performance of SQLServer foreign keys vs triggers to maintain referential integrity (RI) is much better.

I think it should stay as 2 separate scripts because you often want to remove the RI so that data can be imported into the tables quickly. It is much quicker without the RI.

Thanks,

Anthony

Hi Anthony,

I suspected that eg. Oracle driver already had this feature. We don't have the Oracle driver so I can't test it.

Thanks, RogerW.

Hi,

Let's bring this topic to an end. The Sql Server driver should continue to create two scripts. The "Sql integrity"-script shall use real foreign keys (as in the Oracle driver) .

ALTER TABLE DBO.<child table>
ADD CONSTRAINT <foreign key name> FOREIGN KEY <child column>
REFERENCES DBO.<parent table>(<parent column>)
{ON [DELETE|UPDATE] CASCADE}

And, this should not be considered a wish, but a bug! We can't start voting about something that Oracle-users already have in the Uniface driver for Oracle :-).

  Regards RogerW.

Hi RogerW.,

you have my support for 100%

that issues like yours (or RTL support)

MUST NOT be handled the wishful way.

It should be the prime interest of CPWR to add these ASAP to their product.
In the meantime, we have to handle this "by hand", but is this not a "50%-tool, 50% handworkl"

Success, Uli 

Here's an export for simple program to create constraints. Enter your retrieve profile and GOLD-R This will create simple constraints, for more complex ones (where the relationship is constrained by the read trigger on the subtype for example), edit the comments field of the relationship to something like the following. Trigger delete from inner join SOE_NT_HDG on ntt_nth_id = nth_id and nth_entity = '' where = 'Trigger' will be replaced by a create trigger instruction. (So the above becomes as below on my system. CREATE TRIGGER soe_nt_txt_sor_soe_order_del ON SOE_ORDER AFTER DELETE AS delete SOE_NT_TXT from SOE_NT_TXT inner join SOE_NT_HDG on ntt_nth_id = nth_id and nth_entity = 'SOE_ORDER' where (NTT_OCC_ID) = SOR_ID GO

Here's an export for simple program to create constraints.

Enter your retrieve profile and GOLD-R

This will create simple constraints,

for more complex ones (where the relationship is constrained by the read trigger on the subtype for example), edit the comments field of the relationship to something like the following.

Trigger
delete
from inner join SOE_NT_HDG on ntt_nth_id = nth_id and nth_entity = ''
where =

'Trigger' will be replaced by a create trigger instruction. (So the above becomes as below on my system.

CREATE TRIGGER soe_nt_txt_sor_soe_order_del ON SOE_ORDER
AFTER DELETE
AS
delete SOE_NT_TXT
from SOE_NT_TXT inner join SOE_NT_HDG on ntt_nth_id = nth_id and nth_entity = 'SOE_ORDER'
where (NTT_OCC_ID) = SOR_ID
GO

Hi Iain,

nice tool. You hadn't thought about selling it to Compuware?
1000 euro/row of "Local Proc Modules"-trigger :-).

Regards RogerW.

Hmmm, nice thought.

I just noticed that my example override trigger code has had the constants edited out by the forum software. Anyone who can't work them out from context is welcome to mail/message me for details.