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

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