Do foreign keys have to reference primary keys

I have a foreign key question. The manual and I are having a disagreement. THis is IDS 9.3 create table cow foo char (5), bar char (10), bas char (11), woot char (4), primary key (foo, bar, bas) create unique index on cow(foo,bar); Can I do this? create table chicken foo char(5), bar char(10), roost char(5), egg char(4), primary key (roost), foreign key (foo,bar) references cow (foo, bar) foo and bar are in a unique index, they are not the primary key. Can I still refer to them in a foregn key? If so, what is the proper syntax? TIA Dave Thacker

On 4 Jan 2005 22:47:46 -0800, dthacker@omnihotels.com wrote: >I have a foreign key question. The manual and I are having a >disagreement. THis is IDS 9.3 > >create table cow >foo char (5), >bar char (10), >bas char (11), >woot char (4), >primary key (foo, bar, bas)> >create unique index on cow(foo,bar);> >Can I do this? > >create table chicken >foo char(5), >bar char(10), >roost char(5), >egg char(4), >primary key (roost), >foreign key (foo,bar) references cow (foo, bar)> >foo and bar are in a unique index, they are not the primary key. Can I >still >refer to them in a foregn key? If so, what is the proper syntax? >TIA > >Dave Thacker You can do that with: create table cow ( foo char (5), bar char (10), bas char (11), woot char (4), primary key (foo,bar), unique (foo, bar, bas)); create table chicken ( foo char(5), bar char(10), roost char(5), egg char(4), primary key (roost), foreign key (foo,bar) references cow (foo, bar)); But if PK on cow is (foo,bar) than you don't need unique (foo,bar,bas). Nebojsa

dthacker@omnihotels.com wrote: > I have a foreign key question. The manual and I are having a > disagreement. THis is IDS 9.3 > > create table cow > foo char (5), > bar char (10), > bas char (11), > woot char (4), > primary key (foo, bar, bas)> > create unique index on cow(foo,bar);> > Can I do this? > > create table chicken > foo char(5), > bar char(10), > roost char(5), > egg char(4), > primary key (roost), > foreign key (foo,bar) references cow (foo, bar)> > foo and bar are in a unique index, they are not the primary key. Can I > still > refer to them in a foregn key? If so, what is the proper syntax? Another poster has pointed out that adding "unique (foo,bar)" to your first table definition would do the trick. It seems you can only reference a unique constraint or a primary key. A unique index seems not to work. However, if (foo, bar) is unique then why is (foo, bar, bas) your primary key since it does not fit the definition of a primary key as it can be further simplified to (foo, bar) and still remain unique? I can only work with what you have posted so I can't see what other reasons you may have for this. It does seem that rationalising the primary key and looking again at the relations between tables is the obvious answer though. Ben.

The answer was yes, foreign keys DO have to refer to primary keys. The other question posed by the respondents was "Aren't those keys redundant?" Yes, they were. I'll reduce the key in table "cow" to fix this problem. Thanks for your responses. DT

dthacker@omnihotels.com wrote: > The answer was yes, foreign keys DO have to refer to primary keys. The > other question posed by the respondents was "Aren't those keys > redundant?" Yes, they were. I'll reduce the key in table "cow" to > fix this problem. Thanks for your responses. > > DT > No foreign keys DO NOT need to refer a primary constraint, you can reference a unique constraint as well. The following restrictions apply to the column(s) that is specified (the referenced column) in the REFERENCES clause: * The referenced and referencing tables must be in the same database. * The referenced column (or set of columns) must have a unique or primary-key constraint. * The referencing and referenced columns must be the same data type. * You cannot place a referential constraint on a BYTE or TEXT column. * Constraints uses the collation in effect at their time of creation. * A column-level REFERENCES clause can include only a single column name. * The maximum number of columns in a table-level REFERENCES clause is 16. * The total length of the columns in a table-level REFERENCES clause cannot exceed 390 bytes. f IDS IDS If the referenced table is different from the referencing table, you do not need to specify the referenced column; the default column is the primary-key column (or columns) of the referenced table. If the referenced table is the same as the referencing table, you must specify the referenced column. This example will reference thru the primary key on table stock: ALTER TABLE catalog ADD CONSTRAINT (FOREIGN KEY (stock_num, manu_code) REFERENCES stock This example will reference thru a unique constraint on table stock: ALTER TABLE catalog ADD CONSTRAINT (FOREIGN KEY (manu_code, stock_num) REFERENCES stock (manu_code, stock_num)