Thread: references table(multiple columns go here)
Hi folks,
how do I define a referene from 2 columns in 1 table to 2 columns in another.
I have:
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
);
create unique index "ranks_drank_index" on ranks using btree ("rdid",
"rrank");
copy "ranks" from stdin;
1 O 1 Trainee TTI
2 O 2 TTI
3 M 1 Cleaner
4 M 2 Passed Cleaner
5 M 3 Fireman.
\.
I would now like to define the following table so that inserts can only happen
if jdid matches rdid and jrank matches rrank.
create table jobtypes (
jid int4 default nextval('jobs_jid_seq'::text) unique not null,
jdid character references ranks(rdid), -- This joint reference
jrank int4 not null references ranks(rrank), -- needs sorting
jdesc character varying(40) -- job description
);
copy "jobtypes" from stdin;
1 M 3 Charge Cleaner
2 O 3 Lock Carriages
\.
(I want the first row to work and the second to be rejected)
--
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
Hello again
Gary Stainburn wrote:
> Hi folks,
>
> how do I define a referene from 2 columns in 1 table to 2 columns in
> another.
>
> I have:
>
> 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
> );
>
>
>
>
> I would now like to define the following table so that inserts can
> only happen
> if jdid matches rdid and jrank matches rrank.
>
> create table jobtypes (
> jid int4 default nextval('jobs_jid_seq'::text) unique not null,
> jdid character references ranks(rdid), -- This joint reference
> jrank int4 not null references ranks(rrank), -- needs sorting
> jdesc character varying(40) -- job description
!!!here!!! ,CONSTRAINT c2 FOREIGN KEY(jid,jdid) REFERENCES ranks (rid,rdid)
> );
that's all
Tomasz Myrta
Hi Tomasz,
On Wednesday 18 December 2002 4:46 pm, Tomasz Myrta wrote:
> Hello again
>
> Gary Stainburn wrote:
> > Hi folks,
> >
> > how do I define a referene from 2 columns in 1 table to 2 columns in
> > another.
> >
> > I have:
> >
> > 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
> > );
> >
> >
> >
> >
> > I would now like to define the following table so that inserts can
> > only happen
> > if jdid matches rdid and jrank matches rrank.
> >
> > create table jobtypes (
> > jid int4 default nextval('jobs_jid_seq'::text) unique not null,
> > jdid character references ranks(rdid), -- This joint reference
> > jrank int4 not null references ranks(rrank), -- needs sorting
> > jdesc character varying(40) -- job description
>
> !!!here!!! ,CONSTRAINT c2 FOREIGN KEY(jid,jdid) REFERENCES ranks (rid,rdid)
Did this come in with 7.2? I get parse error on or near 'FOREIGN'. Note I
changed the field names to the ones I wanted.
create table jobtypes (
jid int4 default nextval('jobs_jid_seq'::text) unique not null,
jdid character, -- This joint reference
jrank int4 not null references ranks(rrank), -- needs sorting
jdesc character varying(40), -- job description
contraint c2 foreign key (jdid,jrank) references ranks (rdid,rrank)
);
ERROR: parser: parse error at or near "foreign"
>
> > );
>
> that's all
> 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
On Wednesday 18 December 2002 4:56 pm, Gary Stainburn wrote:
> Hi Tomasz,
[snip]
> > > create table jobtypes (
> > > jid int4 default nextval('jobs_jid_seq'::text) unique not null,
> > > jdid character references ranks(rdid), -- This joint reference
> > > jrank int4 not null references ranks(rrank), -- needs sorting
> > > jdesc character varying(40) -- job description
> >
> > !!!here!!! ,CONSTRAINT c2 FOREIGN KEY(jid,jdid) REFERENCES ranks
> > (rid,rdid)
>
> Did this come in with 7.2? I get parse error on or near 'FOREIGN'. Note I
> changed the field names to the ones I wanted.
I've just tried this on a 7.2.1-5 system and get the same error.
>
> create table jobtypes (
> jid int4 default nextval('jobs_jid_seq'::text) unique not null,
> jdid character, -- This joint reference
> jrank int4 not null references ranks(rrank), -- needs sorting
> jdesc character varying(40), -- job description
> contraint c2 foreign key (jdid,jrank) references ranks (rdid,rrank)
> );
> ERROR: parser: parse error at or near "foreign"
>
> > > );
> >
> > that's all
> > 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
Gary Stainburn <gary.stainburn@ringways.co.uk> writes:
> I've just tried this on a 7.2.1-5 system and get the same error.
>> create table jobtypes (
>> jid int4 default nextval('jobs_jid_seq'::text) unique not null,
>> jdid character, -- This joint reference
>> jrank int4 not null references ranks(rrank), -- needs sorting
>> jdesc character varying(40), -- job description
>> contraint c2 foreign key (jdid,jrank) references ranks (rdid,rrank) ^^^^^^^^^
>> );
>> ERROR: parser: parse error at or near "foreign"
If that's an accurate transcription, I think "contraint" -> "constraint"
would help...
regards, tom lane
Tom Lane wrote:
> Gary Stainburn writes:
>
> >I've just tried this on a 7.2.1-5 system and get the same error.
>
>
> >>create table jobtypes (
> >>jid int4 default nextval('jobs_jid_seq'::text) unique not null,
> >>jdid character, -- This joint reference
> >>jrank int4 not null references ranks(rrank), -- needs sorting
> >>jdesc character varying(40), -- job description
> >>contraint c2 foreign key (jdid,jrank) references ranks (rdid,rrank)
>
> ^^^^^^^^^
>
> >>);
> >>ERROR: parser: parse error at or near "foreign"
>
>
> If that's an accurate transcription, I think "contraint" -> "constraint"
> would help...
That's right. The letter has gone somewhere...
Tomasz Myrta
Thanks for that Tom
On Wednesday 18 Dec 2002 5:50 pm, Tom Lane wrote:
> Gary Stainburn <gary.stainburn@ringways.co.uk> writes:
> > I've just tried this on a 7.2.1-5 system and get the same error.
> >
> >> create table jobtypes (
> >> jid int4 default nextval('jobs_jid_seq'::text) unique not null,
> >> jdid character, -- This joint reference
> >> jrank int4 not null references ranks(rrank), -- needs sorting
> >> jdesc character varying(40), -- job description
> >> contraint c2 foreign key (jdid,jrank) references ranks (rdid,rrank)
>
> ^^^^^^^^^
>
> >> );
> >> ERROR: parser: parse error at or near "foreign"
>
> If that's an accurate transcription, I think "contraint" -> "constraint"
> would help...
>
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
);
NOTICE: CREATE TABLE/UNIQUE will create implicit index 'ranks_rid_key' for
table 'ranks'
NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
CREATE
create unique index "ranks_drank_index" on ranks using btree ("rdid",
"rrank");
CREATE
create table jobtypes (
jid int4 default nextval('jobs_jid_seq'::text) unique not null,
jdid character, -- This joint reference
jrank int4 not null references ranks(rrank), -- needs sorting
jdesc character varying(40), -- job description
constraint c2 foreign key (jdid,jrank) references ranks (rdid,rrank)
);
NOTICE: CREATE TABLE/UNIQUE will create implicit index 'jobtypes_jid_key' for
table 'jobtypes'
NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
ERROR: UNIQUE constraint matching given keys for referenced table "ranks" not
found
> regards, tom lane
--
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
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)
>
> );
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.
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.
Tomasz Myrta
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
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
On Thursday 19 Dec 2002 11:30 am, Tomasz Myrta wrote:
> 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
Oops, thought I'd removed that one. Sorry. Thanks for all the help here.
SQL's a totally different thought process to anything I'm used to, but I'm
getting there slowly.
>
> > 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)
I see your point. Maybe I'll have to rethink a bit. As I said, I'm having to
do a lot of rethinking.
>
> 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
--
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
Gary Stainburn <gary.stainburn@ringways.co.uk> writes:
> 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.
No there isn't:
> jrank int4 not null references ranks(rrank), -- needs sorting
^^^^^^^^^^^^^^^^^^^^^^^
You have no index constraining rrank (by itself) to be unique.
regards, tom lane
On Thursday 19 Dec 2002 3:17 pm, Tom Lane wrote: > Gary Stainburn <gary.stainburn@ringways.co.uk> writes: > > 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. > > No there isn't: > > jrank int4 not null references ranks(rrank), -- needs sorting > > ^^^^^^^^^^^^^^^^^^^^^^^ > > You have no index constraining rrank (by itself) to be unique. Thanks for that Tom. The reason that I didn't have that index is because rrank is not unique thus the need for the 2 field constraint that started this thread. I'd just forgot to remove the references clause. Thanks to you too for your help. Between you and Tomasz, I'm making pretty good progress on what is (not very) slowly turning from a small project to the largest database project I've done to date. > > regards, tom lane -- 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