Re: circular relationships ok?

From: Bob Stearns <rstearns1241_at_charter.net>
Date: Thu, 02 Mar 2006 23:58:09 -0500
Message-ID: <E1QNf.331$3Y1.313@fe03.lga>

-CELKO- wrote:

>>>This is an implementation detail that can be got right or got wrong, not an argument for refusing to support the rules of the business. <<

>
>
> The problem is logical, not implementation. In order to get a
> deterministic result, you would need to deter constraints in the same
> order, every time. I suppose the right answer for my A-B-C example
> would be to detect the cycle and do a ROLLBACK when they conflict, but
> run the changes in other cases.
>
>

>>>(It is also, dare I say, a glaring example of why in the 21st century we should be asking why we are still satisfied only to be able to make one incremental update to one row of one table at time.) <<

>
>
> Unh? We can update a **subset** of a single base table at a time; that
> is not a row at time. We did get BEGIN ATOMIC ... END; for
> transactions and defered constraints with SQL-92, so you can do a lot
> of things.
> .
>

If circular relationships are not to be allowed how do we model something like: every set is owned by a user and every user has a last visited set which may not be his. The ddl (DB2) is used is below. The important FK constraints are highlighted.

N.B. The 'GO' lines ae added/needed by my front end.

  CREATE TABLE IS3.SET_HEADERS (

     USERID        	CHARACTER(8) NOT NULL,
     SET_NAME      	CHARACTER(25) NOT NULL,
     DESCRIPTION   	VARCHAR(250),
     DATE_CREATED  	DATE NOT NULL DEFAULT CURRENT DATE,
     DATE_LAST_USED	DATE NOT NULL DEFAULT CURRENT DATE,
     CREATION_LOCN 	INTEGER NOT NULL,
     SHARED        	CHARACTER(1),
     SQL_CODE      	VARCHAR(3500),
     INACTIVE      	CHARACTER(1),
     PRIMARY KEY(USERID,SET_NAME)

)
GO
ALTER TABLE IS3.SET_HEADERS

     ADD CONSTRAINT INACTIVE
	CHECK (INACTIVE in ('Y','N'))

GO
ALTER TABLE IS3.SET_HEADERS

     ADD CONSTRAINT SHARED
	CHECK (SHARED in ('Y','N'))

GO

-------------------------------------------This one-------------------
ALTER TABLE IS3.SET_HEADERS
     ADD CONSTRAINT USERID_FK
	FOREIGN KEY(USERID)
	REFERENCES IS3.USERS(USERID)
	ON DELETE NO ACTION
	ON UPDATE NO ACTION

GO

---

ALTER TABLE IS3.SET_HEADERS

     ADD CONSTRAINT CREATION_LOCN_FK
	FOREIGN KEY(CREATION_LOCN)
	REFERENCES IS3.LOCATIONS(LOC_ID)
	ON DELETE NO ACTION
	ON UPDATE NO ACTION

GO
CREATE INDEX IS3.PRIMARY_SET_H
     ON IS3.SET_HEADERS(USERID, SET_NAME) GO

CREATE TABLE IS3.USERS (

     USERID         	CHARACTER(8) NOT NULL,
     PASSWD         	CHARACTER(8) NOT NULL,
     EMPLOYER_ID    	INTEGER NOT NULL,
     MAX_LOC        	INTEGER NOT NULL,
     PERSONAL_ID    	INTEGER,
     LAST_LOC       	INTEGER,
     LAST_SET_NAME  	CHARACTER(25),
     LAST_SET_USERID	CHARACTER(8),
     PRIMARY KEY(USERID)

)
GO

-----------------------------And this one------------------------------
ALTER TABLE IS3.USERS
     ADD CONSTRAINT USR_LASTS_FK
	FOREIGN KEY(LAST_SET_USERID, LAST_SET_NAME)
	REFERENCES IS3.SET_HEADERS(USERID, SET_NAME)
	ON DELETE NO ACTION
	ON UPDATE NO ACTION

GO

---

ALTER TABLE IS3.USERS

     ADD CONSTRAINT USR_PRES_FK
	FOREIGN KEY(PERSONAL_ID)
	REFERENCES IS3.ENTITIES_PUB(ENTITY_ID)
	ON DELETE NO ACTION
	ON UPDATE NO ACTION

GO
ALTER TABLE IS3.USERS

     ADD CONSTRAINT USR_EMP_FK
	FOREIGN KEY(EMPLOYER_ID)
	REFERENCES IS3.CONTROLLERS(CONTROLLER)
	ON DELETE NO ACTION
	ON UPDATE NO ACTION

GO
ALTER TABLE IS3.USERS

     ADD CONSTRAINT USR_LASTL_FK
	FOREIGN KEY(LAST_LOC)
	REFERENCES IS3.LOCATIONS(LOC_ID)
	ON DELETE NO ACTION
	ON UPDATE NO ACTION

GO Received on Thu Mar 02 2006 - 22:58:09 CST