You are correct! I have a list of groups with descriptions and I can many customers that belong to that group.
I did create an index over the table that has the customers. Then I created the foreign key on the customer table referencing the group table.
When I display my table using DSPFD, it is showing me that it is using the index that I created and the Parent description is the group table and the foreign key is the customer table. It looks write but, I did it backwards to what you said to do.
I'm so confused... :)
-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Rob Berendt
Sent: Wednesday, December 14, 2016 1:24 PM
To: Midrange Systems Technical Discussion
Subject: RE: setting up a foreign key
No. Each customer belongs to a customer group. Right? Let's assume that you have these customer groups
1 - automotive
2 - RV
3 - Furniture
Customer 1, ford, customer group = 1, automotive customer 2, Prevost, customer group 2, RV customer 3, chrysler, customer group = 1, automotive customer 4, Herman Miller, customer group 3, furniture.
So the group would have to exist before the customers. Thus the group would be the parent and the customers the child. Much like an item class would have to exist before an item could be assigned to that class.
Or am I totally misunderstanding your business?
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
From: Tammy Scott <tscott@xxxxxxxxxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Date: 12/14/2016 02:03 PM
Subject: RE: setting up a foreign key
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>
Granted I'm not sure what I'm doing but, Isn't this backwards? Table
'Customer' should be the parent and 'CustomerGroup' should be the child. I
thought the FK should be on the child table of CustomerGroup.
-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Rob
Berendt
Sent: Wednesday, December 14, 2016 10:48 AM
To: Midrange Systems Technical Discussion
Subject: Re: setting up a foreign key
Using this as a start:
CREATE OR REPLACE TABLE rob.CustomerGroup( CompanyId INTEGER NOT NULL,
GroupId Integer not null, GroupDescription VARCHAR(100) ALLOCATE(20),
primary key (CompanyId, GroupId));
CREATE OR REPLACE TABLE rob.Customer(
CompanyId INTEGER NOT NULL,
Account# Integer not null,
GroupId Integer not null,
primary key (CompanyId, Account#));
I tried to go into
http://myibmi:2001 and use the GUI in there to select
multiple keys. I cannot get some of the checking to work. It doesn't
seem to respond to my clicking on it.
Since it's not my problem I won't open a pmr on it but I recommend you do
so.
A work around is to use Run SQL scripts and try this:
alter table rob.Customer
add foreign key(CompanyId, GroupId)
references rob.CustomerGroup (CompanyId, GroupId) on delete no action on
update no action ;
BCCing you an image which the list will strip off.
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
From: Tammy Scott <tscott@xxxxxxxxxxxxxx>
To: "midrange-l@xxxxxxxxxxxx" <midrange-l@xxxxxxxxxxxx>
Date: 12/14/2016 11:19 AM
Subject: setting up a foreign key
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>
I am attempting to setup a foreign key using System I Navigator. I've
never created a foreign key before.
Here is my question and I did my best to explain it. I am very new to
all of this.
Customer table with columns.
CompanyID
Account#
Customergroup
Primary/unique key on customer table is CompanyID, Account#
CustomerGroup table with columns.
CompanyIDgrp
GroupID
GroupDescription
Primary/unique key on CustomerGroup table is CompanyIDgrp, GroupID
I need to setup a foreign key.
Does the Customer table need to have an index that has Customergroup as
part of the key in order for me to setup a foreign key? I cannot figure
out how to choose from the Customer Table, Customergroup.
Thanks for any help.
Tammy
midrange.com
