I want to connect two tables with a foreign key relationship with an unique constraint on the master side instead of primary key.
CREATE TABLE adm_permission (
serno SERIAL PRIMARY KEY,
permissiontype INTEGER,
...
UNIQUE (serno, permissiontype)
);
CREATE TABLE adm_publicpermissions_bnd (
serno SERIAL PRIMARY KEY,
refperm INTEGER NOT NULL,
permissiontype INTEGER NOT NULL,
...
FOREIGN KEY (refperm, permissiontype)
REFERENCES adm_permission(serno, permissiontype),
CHECK (permissiontype IN (1,2,3,4))
);(SERIAL is an Informix way to declare autoincrementing INTEGER column).As you can see, the opposite side of the FK is not, strictly speaking, a
candidate key, it's just a
superkey.
Enterprise Architect (9.0.907) resists to model it. I can't figure, if I do it wrong way, or the EA can't do it at all, or whether it is just a bug.
Here is the procedure I use:
1. I connect the tables dragging the
Quick Link icon from
detail (
adm_publicpermissions_bnd) to
master (
adm_permission):
2. Then I chose, that I want to create a general
association:
3. Then I right-click the newly created association:
4. ... and choos the
Foreign Keys... item from the menu:
5. Finally I chose key columns on both sides of the relationship and hit the
OK button:
EA responds, that "All primary key columns must be selected!", which is totally non-sense, because:
1. I don't want to use the PRIMARY KEY.
2. I've chosen all columns of the superkey I want to use.
3. In fact all columns of the PRIMARY KEY - i.e. the column
serno have actually be selected. (But it's just a word-play.)
