From: Gary Stainburn <gary.stainburn@ringways.co.uk>
To: Tomasz Myrta <jasiek@klaster.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: references table(multiple columns go here)
Date: Thu, 19 Dec 2002 10:30:11 +0000
Message-ID: <200212191030.11661.gary.stainburn@ringways.co.uk> (raw)
In-Reply-To: <3E019829.204@klaster.net>
References: <200212181625.11316.gary.stainburn@ringways.co.uk>
<200212190928.19736.gary.stainburn@ringways.co.uk>
<3E019829.204@klaster.net>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: gary.stainburn@ringways.co.uk, jasiek@klaster.net
Subject: Re: references table(multiple columns go here)
In-Reply-To: <200212191030.11661.gary.stainburn@ringways.co.uk>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox