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

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