Association - Generation specific to Sybase

Referential constraints

Referential constraints are mapped by SQL instructions:

FOREIGN KEY ... REFERENCES.

Note: Only one referential constraint can be specified using the Transact SQL "alter table" command.

Example of referential constraint

SQL generated to specify the referential constraints of an association:

ALTER TABLE role_of_table1

ADD FOREIGN KEY (att1_of_role_of_table1)

REFERENCES table1 (att1)

ALTER TABLE role_of_table1

ADD FOREIGN KEY (att2_of_role_of_table1)

REFERENCES table2 (att2)

Parameterization

Referential constraint names can be parameterized using the SetForeignKeysNames J method that simply has to be redefined under a UML profile. The method’s parameters are as follows:

The ...parameter	represents ...
ComposedClassName	the name of the composed class
fkDest	the destination "foreign key"
fkOrigin	the origin "foreign key"

SetForeignKeysNames method

// Default value for Sybase

AssociationEnd:default#external#Code#RDB#DDL#Sybase#

SetForeignKeysNames

(in String ComposedClassName,

inout String fkOrigin,

inout String fkDest)

{

String fkO;

String fkD;

fkO = SetTableName(ComposedClassName);

fkD = SetTableName(ComposedClassName);

fkO.concat("_OFK");

fkD.concat("_DFK");

fkOrigin = fkO;

fkDest = fkD;

}

// method SetForeignKeysNames

Multiplicity constraints

The generation of tables for Sybase V11 uses triggers to map multiplicity constraints in associations.

Multiplicity constraints are checked after insertion by a trigger named TI_name of the table and after deletion by a trigger named TD_name of the table.

We will generate a multiplicity test through association navigation if the association is mutual with the m-n multiplicity.

Example of multiplicity constraint

SQL generated for an association with 0-5 multiplicity:

CREATE TRIGGER TI_role2_of_class

ON role2_of_class

FOR INSERT AS

IF NOT((SELECT COUNT(*)

FROM role2_of_class, inserted

WHERE role2_of_class.key = inserted.clef) <= 5)

BEGIN

ROLLBACK TRANSACTION

RAISERROR 99999 "role2_of_class : May not insert element,

cardinality constraint violation"

END

Note: In this case, there is no generation of the FOR DELETE trigger to check minimum multiplicity, as it is 0.

The {onDeleteCascade} tagged value

SQL generated for a tuple deletion in the table C1 or C2.

Let us suppose that the role_of_C1 is the table which implements an association between C1 and C2, when a tuple of C1 or C2 is deleted, and that we also want to delete from the table which makes up the association between C1 and C2 all tuples which have the key of the deleted tuple as foreign key.

We will generate a trigger for each class concerned by the association:

CREATE TRIGGER TDCAS_C1

ON C1

FOR DELETE AS

DELETE role_of_C1

FROM role_of_C1, deleted

WHERE role_of_C1.clefC1 = deleted.clefC1

and

CREATE TRIGGER TDCAS_C2

ON C2

FOR DELETE AS

DELETE role_of_C1

FROM role_of_C1, deleted

WHERE role_of_C1.clefC2 = deleted.clefC2