Re: Integrity constraint with jointure

Vincent Lascaux wrote:

> Hello,
>
> I would like to add an integrity constraint on one of my tables, but
> it looks like I need to create a join for that. I would like to have
> any feed back on how to do it, or why the design is bad, and how to
> change to a better one...
>
> create table Teams (
> KeyTeam number primary key
> );
> create table Paths (
> NickName varchar(100) not null,
> KeyTeam references Teams not null,
> Path varchar(500),
> constraint primary key (NickName, KeyTeam)
> );
> create table Users (
> KeyUser number primary key,
> KeyTeam references Teams not null
> );
> create table Tasks (
> KeyUser references Users not null,
> NickNameOfPath varchar(100)
> );
>
> The Tasks table references the Paths table, but the foreign key is the
> team of the user, not the user. So I dont know how to define this
> relation...
>
> Thank you

I presume this is a class project so I'll not give you the solution. What I will tell you is that a quick SELECT constraint_name FROM user_constraints should convince you that this is no way to build constraints. Try using ALTER TABLE instead.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)