Columns in constraint

[Awrel]

Hello,
I am working on a school project - addin to EA to manage Oracle database. I need to know, which columns(and related tables) exist in contraint.
Example:
table CONTAINERS (PK CONTAINER_ID, FK ITEM_ID)
table ITEM_MASTER (PK ITEM_ID)

Now when user makes some changes on table CONTAINERS, column ITEM_ID which is a FK, I need to tell him that this key is from table ITEM_MASTER on column ITEM_ID;

I know how to find tables which I have foreign keys in using SQL, but it's only tables:
"SELECT end_object_id FROM t_connector WHERE (LCASE(connector_type) = 'association' AND start_object_id = " + tableId.ToString() + ") ORDER BY end_object_id"

I thought that this constraint might be in Attribute Constraints, but I get zero from this:
repository1.GetAttributeByID(25).Constraints.Count //table CONTAINERS (ID 16), column ITEM_ID (ID 25)

Maybe it's all wrong, but the information I need can be found in GUI here:
Open table CONTAINERS in project browser, find FK ITEM_ID, right click> "operation properties"> Column> here is the list of Involved columns - ITEM_ID, which is what I need + I need to know from which table this column originates.

I searched entire eap database and found nothing.

Please help.

[Geert Bellekens]

repository1.Get[highlight]Attribute[/highlight]ByID(25).Constraints.Count //table CONTAINERS (ID 16), column ITEM_ID (ID 25)

Open table CONTAINERS in project browser, find FK ITEM_ID, right click> [highlight]"operation properties[/highlight]"> Column> .

Start searching in the operations to begin with.

The involved columns seem to be defined as parameters.

Geert

[Awrel]

Thanks for the reply. I did try it before, but now I got a bit further:

Code: [Select]

EA.Method method1 = repository1.GetElementByGuid("{DEDDFCBE-6DAA-455e-84E7-F719533D2875}").Methods.GetAt(1); //GUID of table CONTAINERS
name = method1.Name; //ITEM_ID_CONTAINERS - this is correct FK name of this table
EA.MethodConstraint methodConstraint1 = method1.PostConditions.GetAt(0); //Index out of bounds, same with PostConditions
Also both tables t_operationposts and t_operationpres seems empty in Access, I am not sure, if I can rely on this. Is PostConditions or PreConditions the correct place where to find details (columns) of the FK ITEM_ID_CONTAINERS?

[Geert Bellekens]

Have you looked at the parameters as I suggested?

Geert

[Awrel]

I did, thanks, it helped me to find what I needed.

[Awrel]

Hello again,
I forgot that primary and foreign keys dont always have same names. For example tables
employer(PK ID)
employee(PK ID, FK EMPLOYER_ID)

When I setup something on employer.id I need to find connected column, which is employee.employer_id

The procedure where I go thru constraints(Table.Method where Table is an Element) and their columns (Table.Method.Parameters) doesn't work here, because I can't connect these two column with different names.

I think I need to use Table.Connectors, but I only get FK names from StyleEx which is for example FK_EMPLOYEE_EMPLOYER, again, I can't resolve concrete column.

I also checked ClientEnd property which leads me to ConnectorEnd object, but it doesn't contain column name.

Long story short, I need the brackets from this picture
https://www.dropbox.com/s/dmy9r3rgsm6r27n/keys.png
(EMPL_ID=ID)
and relevant table names.

Please help.

Awrel

[Geert Bellekens]

Just looking at the contents of the t_connector table should get you further.

I see:
- SourceRole and DestRole which correspond to the names of the FK/PK operations
- Name in the form of (ColumnName = ColumnName)
- StyleEx in the form of "FKINFO=SRC=FK_name :DST=PK_Name:;"

I think it would be best to rely only on the StyleEx field.

Geert

[Awrel]

Thanks for help. I hope it's finally done.

I have null values in column Name in T_CONNECTOR, so I had to use this table to find which FK/PK is involved and then go thru start/end_object_id table.methods.parameter and get the column.

Not pretty but working