Javier: Exactly.
There's no question but the intended FK values exist as PK values prior to running the UPDATE. There are also no RULES under the covers to interfere. I'm going to do an UNLOAD/RELOAD on the database ... way overdue, anyway. Thanks very much. Bruce -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Javier Valencia Sent: Monday, October 14, 2013 1:43 PM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: Ref Integrity Violation? It should work, provided there is a matching value for the primary key. If you try to change a value with a foreign key to a value that does not exist for the primary key you should and will get an error message. Javier, Javier Valencia, PE O: 913-829-0888 H: 913-397-9605 C: 913-915-3137 -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Dennis McGrath Sent: Monday, October 14, 2013 2:21 PM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: Ref Integrity Violation? Razzak, If I read Bruce's email correctly, he was changing FK values, not PK values. What he is trying to do should work. But, I could be wrong, been there, done that. Dennis McGrath Software Developer QMI Security Solutions 1661 Glenlake Ave Itasca IL 60143 630-980-8461 [email protected] -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of A. Razzak Memon Sent: Monday, October 14, 2013 1:25 PM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: Ref Integrity Violation? At 01:25 PM 10/14/2013, Bruce A. Chitiea wrote: >Questions: > >1) Is this a referential integrity error? > >2) Do I drop the FK relationship, run the UPDATE, then re-establish the >relationship? Bruce, In a typical scenario having a table with Primary Key (PK) and associated table(s) with Foreign Key (FK), you cannot change the value of PK Column when there are referenced rows in table(s) with FK. However, in a situation like yours, you can add CASCADE option to the table with PK to automatically update the referenced column in all FK table(s). ADD CASCADE maintains Primary/Foreign key relationships automatically. For example, if you either UPDATE or DELETE a Primary Key value from this table, the corresponding Foreign Key values are updated or deleted automatically. A CASCADE can be applied to UPDATE, DELETE or BOTH to table(s) with specific primary keys. When adding CASCADE, by not specifying either UPDATE or DELETE, both CASCADE restrictions will be enforced upon the Primary/Foreign key tables. Separate UPDATE and DELETE data restrictions can allow a CASCADE to be enforced for records that are updated, but not enforced when records are deleted, in order to avoid an accidental or undesired record delete. Note: CASCADE can only be added to tables with primary keys. Having explained that, you can achieve your goal by using the cool Table Designer (RBDefine) in R:BASE eXtreme 9.5 and simply checking the options for Cascade [x], or use the ALTER TABLE tablename ADD CASCADE command. -- Example 01: Adding CASCADE (Both UPDATE and DELETE) to a table with PK ALTER TABLE tablenamewithPK ADD CASCADE -- Example 02: Adding CASCADE (UPDATE Only) to a table with PK ALTER TABLE tablenamewithPK ADD CASCADE UPDATE -- Example 03: Adding CASCADE (DELETE Only) to a table with PK ALTER TABLE tablenamewithPK ADD CASCADE DELETE That's all there is to it! Have fun. Very Best R:egards, Razzak. www.rbase.com www.facebook.com/rbase -- 30+ years of continuous innovation! 15 Years of R:BASE Technologies, Inc. making R:BASE what it is today! --
