Hi

I have a problem to make constrains across two tables. I am not sure
if this can be down in postgresql.

Here is my table:


Table School
(
id integer,
name text
);


Table Department
(
id integer,
school_id integer reference school(id),
name text
);


Table Course
(
department_id integer references department(id),
name text,
course_number text
)


I would like to make the course_number unique, not in scope of
Department but in the scope of School. So, you may see the same
combination of (department_id, course_number) in the Course table, but
if you pull out (school_id, course_number) list from from a joint
query, you will find the combination is unique. How can I make this
constrain?


Thanks,


ff

> I have a problem to make constrains across two tables. I am not sure
> if this can be down in postgresql.
just store the course-numbers in their own table as primary key
and reference them from the other tables