检查数据库db2中的约束

Question

我正在设计一个数据库，除其他外，有以下实体:

create table Director(
    ID integer not null,
    YearsExperience integer not null,
    StudioAffiliation varchar(30),
    NetWorth REAL,
    primary key (ID)
);
create table Movie(
    ID varchar(20) not null,
    Title varchar(40) not null,
    Genre char(1) not null,
    ReleaseDate date not null,
    Earning real not null,
    primary key (ID)
);

现在导演和电影有2种关系:导演和获奖。后者用于记录导演执导的电影是否获得过奖项或更多奖项:

create table Directs(
    DirectorID integer not null,
    MovieID varchar(20) not null,
    primary key (DirectorID, MovieID),
    foreign key (DirectorID) references Director (ID) on delete cascade,
    foreign key (MovieID) references Movie (ID) on delete cascade
);
create table WonAward(
    DirectorID integer not null,
    MovieID varchar(20) not null,
    AwardName varchar(30) not null,
    Year integer not null,
    Budget REAL not null,
    primary key (DirectorID, MovieID),
    foreign key (DirectorID) references Director (ID) on delete cascade,
    foreign key (MovieID) references Movie (ID) on delete cascade
);

我需要确保导演只能为他/她执导的电影获奖。现在我已经尝试使用Check来实现这一点，但它在WonAward关系结束时一直给我以下2个条件的错误:

check (DirectorID = (SELECT DirectorID FROM Directs WHERE Directs.MovieID = MovieID)),
 check (MovieID = (SELECT MovieID FROM Directs WHERE Directs.DirectorID = DirectorID))

这是我一直得到的错误:

DB21034E  The command was processed as an SQL statement because it was not a 
valid Command Line Processor command.  During SQL processing it returned:
SQL0548N  A check constraint or generated column that is defined with "SELECT" 
is invalid.  SQLSTATE=42621

在没有这些限制的情况下，我发现用户可以在数据库中输入一个导演因一部不是他们导演的电影而获得的奖项。我怎样才能做到呢?

Answer 1

不能创建查询相关行之外数据的检查约束。您可以做的是创建一个BEFORE TRIGGER来验证条件是否满足。在CREATE TRIGGER文档中有一些示例，可以根据您的需要进行定制。

Answer 2

定义一个从WonAward到Directs的外键，而不是:

create table WonAward(
    DirectorID integer not null,
    MovieID varchar(20) not null,
    AwardName varchar(30) not null,
    Year integer not null,
    Budget REAL not null,
    primary key (DirectorID, MovieID),
    foreign key (DirectorID, MovieID) references Directs
);