RE: sql table referential integrity

If the key that is identical in both tables are Key1 and Key2 you cannot
define a referential integrity over the ID!
Both may be independent, because the ID is automatically determined and
there is no guarantee, that both are identical.

In your case you have to work with Triggers!

Mit freundlichen Grüßen / Best regards

Birgitta Hauser


"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
?Train people well enough so they can leave, treat them well enough so they
don't want to.? (Richard Branson)


-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Jay
Vaughn
Sent: Freitag, 31. Januar 2020 14:31
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: sql table referential integrity

so... why am i getting this?

SQL0573 30 184 Position 3 Table FTPDTL in xxxxxx does not have a
matching parent key.

parent...
ftpdtl exists and has... primary key(d_id)

in my new ftppgm table...

i am sure to define the foreign key...
-- foreign key
,d_id
for column did
bigint
not null

and then the alter...

alter table ftppgm
foreign key (d_id)
references ftpdtl(d_id)
on update no action
on delete cascade;

tia

jay


On Fri, Jan 31, 2020 at 7:53 AM Rob Berendt <rob@xxxxxxxxx> wrote:

I'm thinking you'd have to use EITHER the trigger OR the foreign
constraint. I don't think mixing the two would work. So, since the
foreign key constraint falls short you might have to go with the trigger.

Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1 Group Dekko Dept 1600
Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com


-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of
Luis Rodriguez
Sent: Friday, January 31, 2020 7:08 AM
To: Midrange Systems Technical Discussion
<midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: sql table referential integrity

CAUTION: This email originated from outside of the organization. Do
not click links or open attachments unless you recognize the sender
and know the content is safe.


Jay,

I know you prefer not to program this but, What about using triggers?
A DELETE trigger and an UPDATE trigger shouldn't be very hard to code
for your request...

Best Regards,
Luis Rodriguez

--



On Thu, 30 Jan 2020 at 17:40, Jay Vaughn <jeffersonvaughn@xxxxxxxxx>
wrote:

So i have table A (keyed by identity column), and index A (keyed by
key1, key2).

I have table B (keyed by identity column) and index B (keyed by
key1,

key2

- same defined keys as in index A)

table B is really just an "additional information" table for the
rows and keys defined in table A.

How can I apply some kind of referential integrity over table B to
allow table B keyed rows to be deleted if table A similar keyed rows
are deleted? Likewise if table A key value changes, table B key
values

change.

I know I am probably dreaming. I just don't want to go handle this
stuff programmatically.

tia

Jay
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L)
mailing

list

To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our
affiliate
link: https://amazon.midrange.com

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To
subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To
subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link:
https://amazon.midrange.com