Association only unique with attribute

Question

I'm doing a migration of an old data model. The relation between the ADRESSE and the ORGANISATION is a m:n relation with a table ORG_ADRESSE. There are some entries with the same references pairing but different ADRESSTYP_ID. The ilivalidator reports the following error.

OID ...xxx:yyy of object ORG_ADRESSE already exists in ASSOCIATION

The definition looks like that:

   ASSOCIATION ORG_ADRESSE =
      ADRESSEREF -- {0..*} ADRESSE;
      ORGANISATIONREF -- {0..*} ORGANISATION;
      id                            :           INTEGER8;
      ORGANISATION_ID               :           INTEGER8;
      ADRESSE_ID                    :           INTEGER8;
      ADRESSTYP_ID                  :           INTEGER8;
    END ORG_ADRESSE;

What is missing to involve the attribute ADRESSTYP_ID in the uniqueness check?

Answer 1

The uniqueness check is implicit (because its an ASSOCIATION); you can not modify it.

Answer 2

I ran into the same issue last year: https://github.com/claeis/ilivalidator/issues/262

As far as I understand the INTERLIS-Referenzhandbuch (page 34/35) you have to use CARDINALITY larger than one in the association:

Zwischen diesen Objekten ist dann nur eine solche Beziehungsinstanz zulässig. Mehrere Beziehungsinstanzen zwischen derselben Objektkombination sind nur zulässig, wenn für die Beziehung explizit eine Kardinalität mit Obergrenze grösser eins verlangt wird (CARDINALITY). In diesem Fall muss auch eine Objektidentifikation (mittels Property OID) verlangt werden.

Between these objects then only such a relationship instance is permissible. Multiple relationship instances between the same object combination are only permitted if a cardinality with upper limit greater than one is explicitly required for the relationship (CARDINALITY). In this case, an object identification (by means of Property OID) must also be required.

Model:

INTERLIS 2.3;

MODEL AssocDummyModel (de)
AT "https://agi.so.ch"
VERSION "2021-11-05"  =

  TOPIC MyTopic =
    OID AS INTERLIS.UUIDOID;

    CLASS Adresse =
      AttrA : TEXT;
    END Adresse;

    CLASS Organisation =
      AttrO : TEXT;
    END Organisation;

    ASSOCIATION Org_Adresse =
      OID AS INTERLIS.UUIDOID;
      AdresseRef -- {0..*} Adresse;
      OrganisationRef -- {0..*} Organisation;
      AttrAssoz : TEXT;
      CARDINALITY = {*};
    END Org_Adresse;

  END MyTopic;

END AssocDummyModel.

XTF:

<?xml version="1.0" encoding="UTF-8"?>
<TRANSFER xmlns="http://www.interlis.ch/INTERLIS2.3">
  <HEADERSECTION SENDER="ili2gpkg-4.6.0-97c759c6e1adf63b03653871932368050d4ca803" VERSION="2.3">
    <MODELS>
      <MODEL NAME="AssocDummyModel" VERSION="2021-11-05" URI="https://agi.so.ch"/>
    </MODELS>
  </HEADERSECTION>
  <DATASECTION>
    <AssocDummyModel.MyTopic BID="AssocDummyModel.MyTopic">
      <AssocDummyModel.MyTopic.Adresse TID="1542A3E8-1694-4410-BE80-92F7C912AB64">
        <AttrA>Ich bin eine Adresse</AttrA>
      </AssocDummyModel.MyTopic.Adresse>
      <AssocDummyModel.MyTopic.Organisation TID="113BAF61-BA96-4A64-AA8B-21476E5B71B8">
        <AttrO>Ich bin eine Organisation</AttrO>
      </AssocDummyModel.MyTopic.Organisation>
      <AssocDummyModel.MyTopic.Org_Adresse TID="08050F3F-D483-419B-BE2A-62EBE9910506">
        <AdresseRef REF="1542A3E8-1694-4410-BE80-92F7C912AB64"/>
        <OrganisationRef REF="113BAF61-BA96-4A64-AA8B-21476E5B71B8"/>
        <AttrAssoz>Foo</AttrAssoz>
      </AssocDummyModel.MyTopic.Org_Adresse>
      <AssocDummyModel.MyTopic.Org_Adresse TID="8A998959-6FEE-4EB9-B644-D0C81F40A7A5">
        <AdresseRef REF="1542A3E8-1694-4410-BE80-92F7C912AB64"/>
        <OrganisationRef REF="113BAF61-BA96-4A64-AA8B-21476E5B71B8"/>
        <AttrAssoz>Bar</AttrAssoz>
      </AssocDummyModel.MyTopic.Org_Adresse>
    </AssocDummyModel.MyTopic>
  </DATASECTION>
</TRANSFER>

This validates with ilivalidator-1.11.11. But: there seems to be a bug in ili2gpkg (ili2db?). The TID of the association will not be exported (https://github.com/claeis/ili2db/issues/431).

And if you really need/want the UNIQUE constraint (and not only be capable of connecting two objects more then once), you can use a UNIQUE constraint in the association UNIQUE AttrAssoz, AdresseRef, OrganisationRef;

Answer 3

I was just wondering if there is a way to do that by a view. See Interlis Ref page 70.

DOMAIN
 CHSurface = ... ;
FUNCTION Intersect (Surface1: CHSurface;
 Surface2: CHSurface): BOOLEAN;
CLASS A =
 a1: CHSurface;
END A;
CLASS B =
 b1: CHSurface;
END B;
VIEW ABIntersection
 JOIN OF A,B;
 WHERE Intersect (A.a1,B.b1);
 =
END ABIntersection;
ASSOCIATION IntersectedAB
 DERIVED FROM ABIntersection =
 ARole –- A := ABIntersection -> A;
 BRole –- B := ABIntersection -> B;
END IntersectedAB;