foreign keys

[rosmalen]

Hi there,

Must say I'm completely new to Progress databases. But I was wonderingif someone could help me further with a couple of questions.
How do you define a foreign key relationship between to tables (sql statement)?
In which (probably system) tables is that information stored. I justgot the job to administer a Prgress based application, and I want to beable to obtain all information about the foreign key/ primary keyrelation.

anyhelp appreciated.
grts JR

 

[cgdev]

As far as my knowledge, PROGRESS does not support the foreign key concept.
But, you can enforce the foreign concept by DB triggers.

Assume the following Example

Dept Table : Dept No, Dept Name.
Emp Table : Emp No, Emp Name, Dept No Etc..

In this case Dept No in Emp is a foreign key.
By defining a trigger validation on Dept No of Dept Table not to delete any department table when Emp Record exists.

Hello Any DBA Guys please correct me If I am wrong.

 

[rosmalen]

well I dont know if I'm mixing things but in the sql-92 onlinedocumentation there is a statement that I found about foreign keys:
"[size=+3]CONSTRAINT[/size] [size=+3]constraint[/size]_name NOT NULL [ PRIMARY KEY | UNIQUE ] | REFERENCES [ owner_name.]table_name [ ( column_name ) ] | CHECK ( search_condition )

"
http://webapp.progress.com:6336/dyn...fault;ts=default;pt=5078?DwebQuery=constraint

So if this information is recorded it is recorded in some system table,
or maybe its called different in PROGRESS...

 

[c_sinuhe]

Yes, the constraint exist in progress

the constraint exist in progress but, you had have caution because when you create a constrain from sql92 this element is´t validate by a 4GL statement.

you can create a constrain only in the sametime the create table

create table table_name(
column_name1 type constraint cntr_name not null primary key
column_name2 type REFERENCES parent_table(parent_column)
.
.
)

the refereneces statement create a constriant like a foreign key

Now, i have a problem with that.

if you have created table's you can´t apply alter table for include the constrain because the driver throw the follow error :

sorry, operation not implemented yet.

if can you solver this problem? i´ll greate you.

 

[Lee Curzon]

Must say I'm completely new to Progress databases. But I was wonderingif someone could help me further with a couple of questions.
How do you define a foreign key relationship between to tables (sql statement)?
In which (probably system) tables is that information stored. I justgot the job to administer a Prgress based application, and I want to beable to obtain all information about the foreign key/ primary keyrelation.

Hello rosmalen,

4GL:

If the application is 4GL based, then you need to know that Progress doesn't recognise relationships explicitly (apart from the OF clause).

Relationships are programmer defined and maintained (or ignored) through application code (including DB trigger procedures).

You can run the relationships report in the Data Dictionary, but be aware that all it is doing is reporting fields with the same name in different tables - it's a 'first guess' approach.


SQL:

Someone else will have to help you with that, and you might try http://www.peg.com/lists/sql92/web/, but here's a starter:

KB P67059
Title: "What is a Foreign Key"

http://tinyurl.com/b37z4

HTH

Lee

 

[Lee Curzon]

Ok, I just realised the original post was a year ago, but I'll leave the answer anyway.