Foreign key referencing non primary key

[Oyvind]

I am in the process of changing a database schema (SQL Server) to use synthetic keys without changing the business logic in the database (Foreign key relationships)

As I have understood it, a foreign key in one table should be able to reference a candidate (non primary) key in another table as long as the target key is UNIQUE/NOT NULL. But I cannot find a way to model this in EA..  It just gives me an error saying "All primary key columns must be selected!" Am I doing something wrong?

I can append the database with this kind of relationships manually (eg SQL Server accepts it), but would rather have it drawn in the EA database diagram. (So that I could just generate the DDL later)

Below is given an example of what I want to model

CREATE TABLE ENTITYSCHEMA (
      ID bigint identity(1,1)  NOT NULL,
      NAME nvarchar(50) NOT NULL,
      CLASSID bigint NOT NULL
);

ALTER TABLE ENTITYSCHEMA ADD CONSTRAINT PK_ENTITYSCHEMA
      PRIMARY KEY CLUSTERED (ID)
;

ALTER TABLE ENTITYSCHEMA
      ADD CONSTRAINT UQ_ENTITYSCHEMA_CLASSID UNIQUE (ID, CLASSID)
;

CREATE TABLE SCHEMAPROPERTY (
      ID bigint identity(1,1)  NOT NULL,
      SCHEMAID bigint NOT NULL,
      PROPERTYID bigint NOT NULL,
      CLASSID bigint NOT NULL
}

ALTER TABLE SCHEMAPROPERTY ADD CONSTRAINT PK_SCHEMAPROPERTY
      PRIMARY KEY CLUSTERED (ID)
;

ALTER TABLE SCHEMAPROPERTY ADD CONSTRAINT FK_SCHEMAPROPERTY_ENTITYSCHEMA
      FOREIGN KEY (SCHEMAID, CLASSID) REFERENCES ENTITYSCHEMA (ID, CLASSID)
;

[hd]

This issue was resolved in build 817.

I suggest an upgrade to the current release version 7.0, build 818.

[Oyvind]

This issue was resolved in build 817.

I suggest an upgrade to the current release version 7.0, build 818.

I just upgraded to 7.1.827.

The problem remains. EG: This was not resolved in version 7.0, build 817, less the error has been re-introduced..

[«Midnight»]

Did you rebuild the model in the new version?

I am not sure, but...

If not it is possible you've ported the problem to your new version. I think you'll have to remove the old keys - remember to remove both the relations and the operations from both sides - and recreate them as described in the documentation.

I had a similar issue (though not quite the same thing) around the time 6.5 came out, and the only solution was to rebuild my models. A real PITA while you're doing it, but the pain fades.

David

[Eve]

Hi,

If your problem wasn't resolved, then I would suggest that it was probably a different problem and that sparx systems will need to know the details so they can fix it.  So please send in a bug report.

[sargasso]

Purely as a matter of (non-)academic interest why would you want to create unique constraint:
ALTER TABLE ENTITYSCHEMA
     ADD CONSTRAINT UQ_ENTITYSCHEMA_CLASSID UNIQUE (ID, CLASSID);
containing the primary key?  

I've tried a couple thoughts and the only thing I can come up with is accelerated partial key searches.  But when I tried it, in postgresql at least it just optimises to a PK search anyway. (But maybe that was just because f the small sample table I used?)

bruce

[Oyvind]

Did you rebuild the model in the new version?

I am not sure, but...

If not it is possible you've ported the problem to your new version. I think you'll have to remove the old keys - remember to remove both the relations and the operations from both sides - and recreate them as described in the documentation.

I had a similar issue (though not quite the same thing) around the time 6.5 came out, and the only solution was to rebuild my models. A real PITA while you're doing it, but the pain fades.

David

Thank you David for your suggestion. However, the problematic foreign key relationship was not in the model when I upgraded EA, so the problem should not have been ported to the new version. Just to be sure I rebuilt the model in an new project and found that I had the same problem in the new project as well.

Øyvind

[Oyvind]

Purely as a matter of (non-)academic interest why would you want to create unique constraint:
ALTER TABLE ENTITYSCHEMA
     ADD CONSTRAINT UQ_ENTITYSCHEMA_CLASSID UNIQUE (ID, CLASSID);
containing the primary key?  

...

bruce

Thanks Bruce, you hinted me to the solution (see below)

[Oyvind]

I found the solution to this to be to introduce a new column to use as primary key and not to reuse part of the old primary key as part of a unique constraint. Thank you guys for all your help!

The following is generated from my model.

CREATE TABLE ENTITYSCHEMA (
      ID bigint identity(1,1)  NOT NULL,
      NAME nvarchar(50) NOT NULL,
      SCHEMAID bigint NOT NULL,
      CLASSID bigint NOT NULL
)
;

CREATE TABLE SCHEMAPROPERTY (
      ID bigint NOT NULL,
      SCHEMAID bigint NOT NULL,
      CLASSID bigint NOT NULL,
      PROPERTYID bigint
)
;


ALTER TABLE ENTITYSCHEMA ADD CONSTRAINT PK_ENTITYSCHEMA
      PRIMARY KEY CLUSTERED (ID)
;

ALTER TABLE SCHEMAPROPERTY ADD CONSTRAINT PK_SCHEMAPROPERTY
      PRIMARY KEY CLUSTERED (ID)
;


ALTER TABLE ENTITYSCHEMA
      ADD CONSTRAINT UQ_ENTITYSCHEMA_SCHEMAID_CLASSID UNIQUE (SCHEMAID, CLASSID)
;


ALTER TABLE SCHEMAPROPERTY ADD CONSTRAINT FK_SCHEMAPROPERTY_ENTITYSCHEMA
      FOREIGN KEY (SCHEMAID, CLASSID) REFERENCES ENTITYSCHEMA (SCHEMAID, CLASSID)
;

[«Midnight»]

Excellent Øyvind,

The main thing is that you are up and running. I thought bruce made a pretty good point, and fortunately it worked for you. [It also saved me the trouble of trying this out on another platform. Even if it worked I think it might become problematical over time.]

Thanks for posting the resulting structure. It will help others that try this out.

David

[Paolo F Cantoni]

Purely as a matter of (non-)academic interest why would you want to create unique constraint:
ALTER TABLE ENTITYSCHEMA
     ADD CONSTRAINT UQ_ENTITYSCHEMA_CLASSID UNIQUE (ID, CLASSID);
containing the primary key?  

Another reason is to cascade values for denormalization.

But in ALL cases, it should be an explicit decision for a defined reason.

Paolo