Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2002
    Posts
    833

    Unanswered: access and refential integrity constraints

    Hi,

    from powerdesigner I've generated a script to generate a schema in access 97.

    the tables are generated but not the constraints
    what is missing

    here's the log

    Database Generation
    Generation: Check model starting...
    Generation: Check model successful.
    Sorting objects...
    Sort completed.
    Script Generation...
    Creating Tables...
    -> Table: Anwendung (Anwendung)
    -> Table: Anwendung_Schnittstellen (Anwendung_Schnittstellen)
    -> Table: Anwendungsdienst (Anwendungsdienst)
    -> Table: Betriebssystem (Betriebssystem)
    -> Table: Datendienst (Datendienst)
    -> Table: Knoten (Knoten)
    -> Table: Leistungsparameter (Leistungsparameter)
    -> Table: Netzwerkdienst (Netzwerkdienst)
    -> Table: Organisationseinheit (OE)
    -> Table: Partner (Partner)
    -> Table: Praesentation (Praesentation)
    -> Table: Schicht (Schicht)
    -> Table: Subsystem (Subsystem)
    -> Table: System (System)
    -> Table: Verteileinheit (Verteileinheit)
    -> Table: anwendung_oe (anwendung_oe)
    -> Table: knoten_system (knoten_system)
    -> Table: status (status)
    -> Table: verteileinheit_knoten (verteileinheit_knoten)
    -> Table: verteileinheit_subsystem (verteileinheit_subsystem)
    Creating References...
    -> Reference: anwendung_partner_dghyp (anwendung_partner_dghyp)
    -> Reference: anwendung_partner_ipo (anwendung_partner_ipo)
    -> Reference: anwendung_partner_kw (anwendung_partner_kw)
    -> Reference: anwendung_schnittstelle_status
    (anwendung_schnittstelle_status)
    -> Reference: subsystem_schnittstellen (subsystem_schnittstellen)
    -> Reference: Knoten_anwendungsdienst (knoten_anwendungsdienst)
    -> Reference: knoten_datendienst (knoten_datendienst)
    -> Reference: knoten_leistungsparameter (knoten_leistungsparameter)
    -> Reference: knoten_netzwerkdienste (knoten_netzwerkdienste)
    -> Reference: partner_oe (partner_oe)
    -> Reference: knoten_praesentation (knoten_praesentation)
    -> Reference: knoten_schicht (knoten_schicht)
    -> Reference: subsystem_anwendung (subsystem_anwendung)
    -> Reference: system_betriebssystem (system_betriebssystem)
    -> Reference: anwendung_oe (anwendung_oe)
    -> Reference: anwendung_oe (anwendung_oe2)
    -> Reference: knoten_system (knoten_system)
    -> Reference: knoten_system (knoten_system2)
    -> Reference: verteileinheit_knoten (verteileinheit_knoten)
    -> Reference: verteileinheit_knoten (verteileinheit_knoten2)
    -> Reference: verteileinheit_subsystem (verteileinheit_subsystem)
    -> Reference: verteileinheit_subsystem (verteileinheit_subsystem2)
    Script Generation completed
    *** Executing statement 1:
    create table Anwendung
    (
    id_anwendung COUNTER not null,
    id_partner_dgh INTEGER not null,
    id_partner_kw INTEGER not null,
    id_partner_ipo INTEGER not null,
    kurzname TEXT(32) not null,
    kurzbeschreibung TEXT(255),
    langname TEXT(64),
    anwendungstyp TEXT(32) not null,
    art_der_software TEXT(32) not null,
    datum_prod_start DATETIME not null,
    datum_erstellung DATETIME not null,
    datum_letzte_aenderung DATETIME,
    ziele TEXT(255),
    bemerkung TEXT(255)
    )
    *** Statement successfully executed.

    *** Executing statement 2:
    create table Anwendung_Schnittstellen
    (
    id_subsystem INTEGER not null,
    id_schnittstellen COUNTER not null,
    id_status INTEGER not null,
    name TEXT(32) not null,
    kurzname TEXT(32),
    in_out_update TEXT(32),
    automatismus TEXT(32),
    synchon_async YESNO not null,
    verbindungstyp TEXT(32),
    technik TEXT(32),
    wesentl_fach_obj TEXT(32),
    mengengeruest TEXT(64)
    )
    *** Statement successfully executed.

    *** Executing statement 3:
    create table Anwendungsdienst
    (
    id_anwendungsdienst INTEGER not null,
    id_knoten INTEGER,
    name TEXT(32) not null
    )
    *** Statement successfully executed.

    *** Executing statement 4:
    create table Betriebssystem
    (
    id_betriebssystem COUNTER not null,
    betriebssystem TEXT(32) not null
    )
    *** Statement successfully executed.

    *** Executing statement 5:
    create table Datendienst
    (
    id_datendienst INTEGER not null,
    id_knoten INTEGER,
    datendienst_beschreibung TEXT(64)
    )
    *** Statement successfully executed.

    *** Executing statement 6:
    create table Knoten
    (
    id_knoten COUNTER not null,
    name TEXT(32) not null,
    schicht TEXT(32),
    betriebssystem TEXT(32) not null
    )
    *** Statement successfully executed.

    *** Executing statement 7:
    create table Leistungsparameter
    (
    id_leistungsparameter COUNTER not null,
    id_knoten INTEGER not null,
    leistungsparameter INTEGER
    )
    *** Statement successfully executed.

    *** Executing statement 8:
    create table Netzwerkdienst
    (
    id_netwerkdienst COUNTER not null,
    id_knoten INTEGER not null,
    netzwerkdienst TEXT(32) not null
    )
    *** Statement successfully executed.

    *** Executing statement 9:
    create table OE
    (
    id_oe COUNTER not null,
    bezeichnung TEXT(32) not null
    )
    *** Statement successfully executed.

    *** Executing statement 10:
    create table Partner
    (
    id_partner COUNTER not null,
    id_oe INTEGER not null,
    name TEXT(32) not null,
    vorname TEXT(32) not null,
    telefon INTEGER not null
    )
    *** Statement successfully executed.

    *** Executing statement 11:
    create table Praesentation
    (
    id_praesentation COUNTER not null,
    id_knoten INTEGER,
    name TEXT(32) not null
    )
    *** Statement successfully executed.

    *** Executing statement 12:
    create table Schicht
    (
    id_schicht COUNTER not null,
    id_knoten INTEGER,
    beschreibung TEXT(64) not null
    )
    *** Statement successfully executed.

    *** Executing statement 13:
    create table Subsystem
    (
    id_subsystem COUNTER not null,
    id_anwendung INTEGER,
    beschreibung TEXT(64) not null
    )
    *** Statement successfully executed.

    *** Executing statement 14:
    create table System
    (
    id_system COUNTER not null,
    id_betriebssystem INTEGER not null,
    name TEXT(32) not null,
    hersteller TEXT(32) not null,
    modell TEXT(32) not null,
    standort TEXT(32) not null,
    raum TEXT(32) not null,
    eigentuemer TEXT(32) not null
    )
    *** Statement successfully executed.

    *** Executing statement 15:
    create table Verteileinheit
    (
    id_verteileinheit COUNTER not null,
    name TEXT(32) not null,
    verbrauchsinformation TEXT(255),
    besonderheiten TEXT(255),
    plattform TEXT(32) not null
    )
    *** Statement successfully executed.

    *** Executing statement 16:
    create table anwendung_oe
    (
    id_oe INTEGER not null,
    id_anwendung INTEGER not null
    )
    *** Statement successfully executed.

    *** Executing statement 17:
    create table knoten_system
    (
    id_knoten INTEGER not null,
    id_system INTEGER not null
    )
    *** Statement successfully executed.

    *** Executing statement 18:
    create table status
    (
    id_status COUNTER not null,
    status TEXT(1) not null,
    beschreibung TEXT(64) not null
    )
    *** Statement successfully executed.

    *** Executing statement 19:
    create table verteileinheit_knoten
    (
    id_verteileinheit INTEGER not null,
    id_knoten INTEGER not null
    )
    *** Statement successfully executed.

    *** Executing statement 20:
    create table verteileinheit_subsystem
    (
    id_verteileinheit INTEGER not null,
    id_subsystem INTEGER not null
    )
    *** Statement successfully executed.

    *** Executing statement 21:
    alter table Anwendung
    add constraint FK_ANWENDUN_ANWENDUNG_PARTNER3 foreign key
    (id_partner_kw)
    references Partner (id_partner)
    *** Execution of statement failed:
    [Microsoft][ODBC Microsoft Access Driver] Es wurde kein eindeutiger Index
    für das in Beziehung stehende Feld der Primärtabelle angegeben.
    SQLSTATE = S1000

    *** Executing statement 22:
    alter table Anwendung
    add constraint FK_ANWENDUN_ANWENDUNG_PARTNER foreign key
    (id_partner_ipo)
    references Partner (id_partner)
    *** Execution of statement failed:
    [Microsoft][ODBC Microsoft Access Driver] Es wurde kein eindeutiger Index
    für das in Beziehung stehende Feld der Primärtabelle angegeben.
    SQLSTATE = S1000

    *** Executing statement 23:
    alter table Anwendung
    add constraint FK_ANWENDUN_ANWENDUNG_PARTNER2 foreign key
    (id_partner_dgh)
    references Partner (id_partner)
    *** Execution of statement failed:
    [Microsoft][ODBC Microsoft Access Driver] Es wurde kein eindeutiger Index
    für das in Beziehung stehende Feld der Primärtabelle angegeben.
    SQLSTATE = S1000

    and all other contraints where rejected, too.

    any help highly appreciated

  2. #2
    Join Date
    Mar 2003
    Posts
    1
    Hi,

    You cannot add constraints (define foreign key) when there is no index for the table.field.

    In the 'Database' - 'Generate Database' options, did you check 'create index' checkbox?

    Powerdesigner should then create indexes prior to defining foreign keys.

    Bye

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •