Constraints to Guarantee unique across tables with foreign key?

[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);

so that for a given student, there can be one and only one of any particular
schoolday. Can this be done with constraints?

-Ben

--
"I kept looking around for somebody to solve the problem.
Then I realized I am somebody"
-Anonymous
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Janning Vygen]

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

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Benjamin Smith]

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
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html