Thread: Re: constrains on two tables

On Sep 10, 9:55 pm, finecur <fine...@yahoo.com> wrote:
> Hi
>
> 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, but not in the course
> table, nor in department. I would like to make the course_number
> unique in the scope of School. So, you may see the same course_number
> in Course table, but (school_id, course_number) should be always
> unique. How can I make the constrain?
>
> Thanks,
>
> ff

Anyone know???

In response to finecur <finecur@yahoo.com>:

> On Sep 10, 9:55 pm, finecur <fine...@yahoo.com> wrote:
> > Hi
> >
> > 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, but not in the course
> > table, nor in department. I would like to make the course_number
> > unique in the scope of School. So, you may see the same course_number
> > in Course table, but (school_id, course_number) should be always
> > unique. How can I make the constrain?

ALTER TABLE Course PRIMARY KEY(school_id, course_number);

and add the school_id column to the Course table.

--
Bill Moran
http://www.potentialtech.com