Helen Borrie wrote:

>
> On what basis do you think FB could "smart-guess" or determine an optimal
> sequence for two constraints that are unrelated to each other?

If that can not be done, then a mechanism for
controlling (and checking) foreign key check-and-cascade orders would be
nice.

> At best, it might perform the cascade on the basis of the creation order of
> the foreign keys, or some other arbitrary metadata thing -- alphabetical
> order? Have you considered running your own predictability tests?

I have attached the two scripts that i was talking about previously.

(I'm sorry for cutting in to this thread, but it has everything to do
with my last post (s: fk problem on 07.03.03))


BR,
Lauri

----------

CREATE DATABASE 'E:\test0.GDB' USER 'SYSDBA' PASSWORD 'masterkey';

/* this order prevents changeing project manager name after it has been used in projects */

CREATE TABLE PROJECTMANAGERS (
NAME VARCHAR(40) NOT NULL,
ID INTEGER NOT NULL
);


CREATE TABLE GROUPS (
GROUP_NAME VARCHAR(40) NOT NULL,
MANAGER_NAME VARCHAR(40) NOT NULL,
ID INTEGER NOT NULL
);

CREATE TABLE PROJECTS (
/* (lots of other fields..) */
ID INTEGER NOT NULL,
GROUP_NAME VARCHAR(40),
PROJECTMANAGER VARCHAR(40)
);

ALTER TABLE GROUPS ADD CONSTRAINT UNQ_GROUPS UNIQUE (GROUP_NAME, MANAGER_NAME);
ALTER TABLE PROJECTMANAGERS ADD CONSTRAINT UNQ_PROJECTMANAGERS UNIQUE (NAME);

ALTER TABLE PROJECTS ADD CONSTRAINT PK_PROJECTS PRIMARY KEY (ID);
ALTER TABLE GROUPS ADD CONSTRAINT PK_GROUPS PRIMARY KEY (ID);
ALTER TABLE PROJECTMANAGERS ADD CONSTRAINT PK_PROJECTMANAGERS PRIMARY KEY (ID);

ALTER TABLE PROJECTS ADD CONSTRAINT FK_PROJECTS_GROUPS FOREIGN KEY (GROUP_NAME, PROJECTMANAGER) REFERENCES GROUPS (GROUP_NAME, MANAGER_NAME) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE PROJECTS ADD CONSTRAINT FK_PROJECTS_PROJECTMANAGER FOREIGN KEY (PROJECTMANAGER) REFERENCES PROJECTMANAGERS (NAME) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE GROUPS ADD CONSTRAINT FK_GROUPS FOREIGN KEY (MANAGER_NAME) REFERENCES PROJECTMANAGERS (NAME) ON DELETE SET NULL ON UPDATE CASCADE;

COMMIT;

INSERT INTO PROJECTMANAGERS (NAME, ID) VALUES ('John Doe', 1);
INSERT INTO PROJECTMANAGERS (NAME, ID) VALUES ('Jane Doe', 2);

INSERT INTO GROUPS (GROUP_NAME, MANAGER_NAME, ID) VALUES ('Jane''s', 'Jane Doe', 1);
INSERT INTO GROUPS (GROUP_NAME, MANAGER_NAME, ID) VALUES ('Jane''s second', 'Jane Doe', 2);
INSERT INTO GROUPS (GROUP_NAME, MANAGER_NAME, ID) VALUES ('John''s', 'John Doe', 3);

INSERT INTO PROJECTS (GROUP_NAME, PROJECTMANAGER, ID) VALUES ('Jane''s', 'Jane Doe', 1);
INSERT INTO PROJECTS (GROUP_NAME, PROJECTMANAGER, ID) VALUES ('Jane''s second', 'Jane Doe', 2);

COMMIT WORK;

/* trying to change Jane Doe to something else in projectmanagers produces an fk violation */


----------

CREATE DATABASE 'E:\test1.GDB' USER 'SYSDBA' PASSWORD 'masterkey';

/* this fk order works as expected */

CREATE TABLE PROJECTMANAGERS (
NAME VARCHAR(40) NOT NULL,
ID INTEGER NOT NULL
);


CREATE TABLE GROUPS (
GROUP_NAME VARCHAR(40) NOT NULL,
MANAGER_NAME VARCHAR(40) NOT NULL,
ID INTEGER NOT NULL
);

CREATE TABLE PROJECTS (
/* (lots of other fields..) */
ID INTEGER NOT NULL,
GROUP_NAME VARCHAR(40),
PROJECTMANAGER VARCHAR(40)
);

ALTER TABLE PROJECTMANAGERS ADD CONSTRAINT UNQ_PROJECTMANAGERS UNIQUE (NAME);
ALTER TABLE PROJECTMANAGERS ADD CONSTRAINT PK_PROJECTMANAGERS PRIMARY KEY (ID);

ALTER TABLE GROUPS ADD CONSTRAINT UNQ_GROUPS UNIQUE (GROUP_NAME, MANAGER_NAME);
ALTER TABLE GROUPS ADD CONSTRAINT PK_GROUPS PRIMARY KEY (ID);
ALTER TABLE GROUPS ADD CONSTRAINT FK_GROUPS FOREIGN KEY (MANAGER_NAME) REFERENCES PROJECTMANAGERS (NAME) ON DELETE SET NULL ON UPDATE CASCADE;

ALTER TABLE PROJECTS ADD CONSTRAINT PK_PROJECTS PRIMARY KEY (ID);
ALTER TABLE PROJECTS ADD CONSTRAINT FK_PROJECTS_GROUPS FOREIGN KEY (GROUP_NAME, PROJECTMANAGER) REFERENCES GROUPS (GROUP_NAME, MANAGER_NAME) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE PROJECTS ADD CONSTRAINT FK_PROJECTS_PROJECTMANAGER FOREIGN KEY (PROJECTMANAGER) REFERENCES PROJECTMANAGERS (NAME) ON DELETE SET NULL ON UPDATE CASCADE;

COMMIT;

INSERT INTO PROJECTMANAGERS (NAME, ID) VALUES ('John Doe', 1);
INSERT INTO PROJECTMANAGERS (NAME, ID) VALUES ('Jane Doe', 2);

INSERT INTO GROUPS (GROUP_NAME, MANAGER_NAME, ID) VALUES ('Jane''s', 'Jane Doe', 1);
INSERT INTO GROUPS (GROUP_NAME, MANAGER_NAME, ID) VALUES ('Jane''s second', 'Jane Doe', 2);
INSERT INTO GROUPS (GROUP_NAME, MANAGER_NAME, ID) VALUES ('John''s', 'John Doe', 3);

INSERT INTO PROJECTS (GROUP_NAME, PROJECTMANAGER, ID) VALUES ('Jane''s', 'Jane Doe', 1);
INSERT INTO PROJECTS (GROUP_NAME, PROJECTMANAGER, ID) VALUES ('Jane''s second', 'Jane Doe', 2);

COMMIT WORK;

/* trying to change Jane Doe to something else in projectmanagers cascades the changes where intended */


[Non-text portions of this message have been removed]