Skip site navigation (1) Skip section navigation (2)

Re: Constraints to Guarantee unique across tables with foreign key?

From: Benjamin Smith <bens(at)effortlessis(dot)com>
To: Janning Vygen <vygen(at)gmx(dot)de>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Constraints to Guarantee unique across tables with foreign key?
Date: 2004-08-26 22:04:29
Message-ID: 200408261504.29802.bens@effortlessis.com (view raw or flat)
Thread:
Lists: pgsql-general
Thanks!

 Using the dual foreign key essentially allows me to "import" the students_id 
field from attendancereports into attendancerecords, thereby satisfying my 
requirement to guarantee uniqueness, even though students_id is really more 
applicable to the attendancereports table. 

(I can't drop attendancereports table for the reason that the report itself 
needs to be tracked in order to be sure all forms have been turned in and 
signed - I did not mention additional fields to guarantee that the form has 
been verified by another staff member, and is thus "on file".) 

I started SQL (as do many) with MySQL, and very quickly became frustrated with 
its limitations. I moved to PG, and now, even after developing dozens of 
applications over the ensuing half decade, am continuously amazed by the fact 
that whatever problem I run into, Postgres can handle it. 

Damn nice software.... 

-Ben 

On Thursday 26 August 2004 01:15, Janning Vygen wrote:
> Am Donnerstag, 26. August 2004 04:43 schrieb Benjamin Smith:
> > I have two tables like following:
> >
> > create table attendancereport (
> > id serial unique not null,
> > staff_id integer not null references staff(id),
> > schoolyear varchar not null references schoolyear(year),
> > students_id integer not null references students(id)
> > );
> >
> > // schoolyear.year in format "2003 - 2004".
> >
> > Create table attendancerecords (
> > attendancereport_id integer not null references attendancereport(id),
> > schoolday integer not null references schooldays(day),
> > attended bool not null
> > );
> >
> > // schoolday.day in formation YYYYMMDD as in 200301222 for dec 22, 2003.
> >
> > What I'm looking for is a way to create a unique( ) across tables via the
> > foriegn key, something like
> >
> > Alter table attendancerecords
> > ADD unique (schoolday, attendancereport.students_id);
> 
> You need mutliple column foreign keys like this (didnt test it just typed 
and 
> its early in the morning, havn't got any coffee yet):
> 
> CREATE TABLE attendancereport (
>  students_id integer NOT NULL REFERENCES students(id),
>  schoolyear  varchar NOT NULL REFERENCES schoolyear(year), 
>  staff_id    integer NOT NULL REFERENCES staff(id),
>  CONSTRAINT pk_arep PRIMARY KEY (students_id, schoolyear)
> );
> 
> CREATE TABLE attendancerecords (
>   students_id integer NOT NULL,
>   schoolyear  varchar NOT NULL, 
>   schoolday   integer NOT NULL REFERENCES schooldays(day),
>   attended    boolean  NOT NULL,
>   CONSTRAINT pk_arec PRIMARY KEY (students_id, schoolyear, schoolday),
>   CONSTRAINT fk_students_id FOREIGN KEY (students_id, schoolyear) 
>              REFERENCES attendancereport(students_id, schoolyear)
> );
> 
> this way you can have only ONE unique record for each student on each day of 
> any schoolyear. The Uniqueness is guranteed by the Primary key (which is in 
> theory nothing else like a uniquey key which is NOT NULL)
> 
> I dropped the serial columns because i dont know what those surrogate keys 
are 
> for, but you can add them again, if you want to select records by number 
> within your application. 
> 
> [Maybe you could even place the staff_id field into your students table and 
> drop the table attendancereport.]
> 
> kind regards,
> janning
> 

-- 
"I kept looking around for somebody to solve the problem. 
Then I realized I am somebody" 
   -Anonymous


In response to

pgsql-general by date

Next:From: Kevin MurphyDate: 2004-08-26 22:24:23
Subject: performance of IN (subquery)
Previous:From: Mark DexterDate: 2004-08-26 21:42:14
Subject: Creating Functions in Separate Schema

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group