wakalaka wrote:
> 1) im trying to create a table for goaliestats
> because goalie stats are seperate from player
> stats. So i wanted to create the table to allow
> only players that are of position goalie (G). my
> create table looks liek this
> CREATE TABLE Gstats(
> pid NUMBER(3),
> shotsfaced NUMBER(4),
> goalsallowed NUMBER(3),
> PRIMARY KEY (pid),
> FOREIGN KEY (pid) REFERENCES Player,
> CONSTRAINT onlyGoalie
> CHECK ('G'= (select pos from player where
> pos='G'))
> );
>
> pos is in player table and the only choices for it
> is F,D,G
> i keep getting the error "subquery not allowed"
> for the select statement. According to my
> textbook im useing this should work.
If you're talking about MySQL, your textbook is wrong. It might be in the Standards but it's not supported by MySQL. MySQL does not (yet) support check constraints. Here're the reference to the latest manual on 5.1 (which is Alpha):
http://dev.mysql.com/doc/refman/5.1/en/create-table.html
"The CHECK clause is parsed but ignored by all storage engines. "
The workaround is to use a BEFORE TRIGGER, here's the manual reference on it:
http://dev.mysql.com/doc/refman/5.1/en/triggers.html
Ulf