Re: [SQL] references table(multiple columns go here)

[Tomasz Myrta]

Gary Stainburn wrote:

On Thursday 19 Dec 2002 9:58 am, Tomasz Myrta wrote:

>Gary Stainburn wrote:
>
>>That did the trick.  However, I now have another problem with the
>>constraint
>>complaining about there not being an index to refer to.  However,
>>there is.

The error is inside declaration of table "ranks.
You can't create two similiar foreign keys: one based on field (rrank) and second one based on fields (rdid,rrank).
You have to change:
jrank int4 not null references ranks(rrank), -- needs sorting
to
jrank int4 not null, -- needs sorting

This is probably because of my background in as a COBOL programmer where
having multiple fields of the same name can cause problems (especially with
MF Cobol which only partially supports it) as well as early (read early 80's)
database experince where it wasn't allowed. Also, I find it usefull because
I know immediately which table a field has come from.

As you wish.

Why does it make joins easier to use the same name for fields?

If you create queries like this, you get rid of duplicates.
select *
from
jobtypes
join departments using (did)

If you are afraid of duplicates, you can always use an alias:
select
 ranks.rank_id as rid,
...

If you want, here is my minimal version of your tables:

create table depts (
 dept_id	int4 primary key,
 ...
};

create table ranks (
rank_id     	int4 default nextval('ranks_rid_seq') primary key,
dept_id		int4 references depts, -- department
rank		int4 not null,		-- departmental rank
rdesc		character varying(40)	-- Rank Description
);

create table jobtypes (
jobtype_id     	int4 default nextval('jobs_jid_seq') primary key,
rank_id		int4 references ranks(rank_id),
jdesc		character varying(40)	-- job description
);

Tomasz Myrta


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly