> >CREATE TABLE ACM_DOUBLEKNOCKITEMS
> >(
> > DNI_DN_ID D_NOT_NULL_ONE ,
> > DNI_AC_ID D_NOT_NULL_ONE ,
> > CONSTRAINT PK_DOUBLEKNOCKITEMS PRIMARY KEY (DNI_DN_ID,
> DNI_AC_ID) );
> >
> >ALTER TABLE ACM_DOUBLEKNOCKITEMS ADD CONSTRAINT FK_DNI_AC_ID
> > FOREIGN KEY (DNI_AC_ID) REFERENCES ALARMCODES
> > (AC_ID)
> > ON DELETE CASCADE
> > ON UPDATE CASCADE;
> >
> >ALTER TABLE ACM_DOUBLEKNOCKITEMS ADD CONSTRAINT FK_DNI_DN_ID
> > FOREIGN KEY (DNI_DN_ID) REFERENCES ACM_DOUBLEKNOCK
> > (DN_ID)
> > ON DELETE CASCADE
> > ON UPDATE CASCADE;
> >

>
> OK...would you post the structures of AlarmCodes and
> DoubleKnock, too...
> what I think you are after is to implement a classic
> Many:Many relationship. - since your DoubleKnockItems record
> is nothing but an intersection structure between DoubleKnock
> and AlarmCodes.

True

> If what you really want to achieve is to match up/create
> intersection records for each occurrence of a DoubleKnock and
> an AlarmCode, then there
> *is* certainly an *easy* right way to do this
>
>
> >Any help regarding the SQL for the detail would be very welcome
>
> Yes, sure, but will you post the structures of AlarmCodes and
> DoubleKnock, including the constraints?
>
> Helen
>

CREATE TABLE ACM_DOUBLEKNOCK
(
DN_ID SMALLINT NOT NULL,
DN_AD_REGID D_NOT_NULL_ZERO ,
DN_AC_ID D_NOT_NULL_ONE ,
DN_PERIOD D_NOT_NULL_ZERO ,
DN_DESCRIPTION VARCHAR( 128) NOT NULL COLLATE NONE,
CONSTRAINT PK_DOUBLEKNOCK PRIMARY KEY (DN_ID)
);

ALTER TABLE ACM_DOUBLEKNOCK ADD CONSTRAINT U_DN_DESCRIPTION UNIQUE
(DN_DESCRIPTION);

Please note that DN_AC_ID is also a FOREIGN KEY REFERENCES ALARMCODES
Please note that DN_AD_REGID is also a FOREIGN KEY,


CREATE TABLE ALARMCODES
(
AC_ID SMALLINT NOT NULL,
AC_TYPEMASK SMALLINT NOT NULL,
AC_ALARMSTR VARCHAR( 128) NOT NULL COLLATE NONE,
AC_DESCRIPTION VARCHAR( 128) NOT NULL COLLATE NONE,
CONSTRAINT PK_ALARMCODES PRIMARY KEY (AC_ID)
);


danieL.
____________________________
Comvision Pty. Ltd.

www.comvision.net.au