Re: references table(multiple columns go here)

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.
> > Output below:
> >
> > create table ranks (
> > rid int4 default nextval('ranks_rid_seq'::text) unique not null,
> > rdid character references depts(did), -- department
> > rrank int4 not null, -- departmental rank
> > rdesc character varying(40) -- Rank Description
>
> ,CONSTRAINT ranks_pkey PRIMARY KEY (rid,rrank)
> or
> ,CONSTRAINT ranks_unq UNIQUE (rid,rrank)
>
> > );

I added the 2nd constraint but used rdid instead of rid as that's the field I
need the constraint on. I also removed the create unique index statement.

I ended up with the same result tho' - the ranks_unq constraint created an
index with the same definition as the one created by 'create unique index'.

I still get the same error when trying to create the constraint on the
jobtypes table.

>
> Create primary key on two fields in table ranks, or at least create
> unique constraint on them.
>
> If rid is unique, why do you use two fields as foreign key? "rid" is
> enough. You can get rid of "rrank" in table jobtypes.

'rid' is the primary key and is used as a reference from other tables for ease
as much as anything.

The rdid,rrank pair I want as a constraint for data integrity reasons.

>
> And one more question - why you don't use the same names in all tables?
> "did" instead of "did" "rdid" "jdid" ? It's much easier to create joins
> when using the same names.

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.

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

> Tomasz Myrta

--
Gary Stainburn

This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000