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

Constraints to Guarantee unique across tables with foreign key?

From: Benjamin Smith <bens(at)effortlessis(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Constraints to Guarantee unique across tables with foreign key?
Date: 2004-08-26 02:43:03
Message-ID: 200408251943.03538.bens@effortlessis.com (view raw or flat)
Thread:
Lists: pgsql-general
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


Responses

pgsql-general by date

Next:From: Jim C. NasbyDate: 2004-08-26 03:02:44
Subject: Re: Gentoo for production DB server?
Previous:From: Martijn van OosterhoutDate: 2004-08-26 02:35:48
Subject: Re: Possible to insert quoted null value into integer field?

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