Date datatype problem with MySql?

[nomadsoul]

I get errors when creating the below table except when I remove:
PAYMENT_DATE DATE NOT NULL
CHECK(PAYMENT_DATE >= DATE('1969-12-31')),

Then, everything is fine. Table created no problem.
I don't see anything wrong with the syntax. Could the date datatype be incompatible with the version of MySql I'm using (I'm using FQ's MySql)?
Really confused with this. I don't even know how to ask google about this.
I'm probably overlooking the obvious as usual.


CREATE TABLE PENALTIES
(PAYMENTNO INTEGER NOT NULL PRIMARY KEY,
PLAYERNO INTEGER NOT NULL,
PAYMENT_DATE DATE NOT NULL
CHECK(PAYMENT_DATE >= DATE('1969-12-31')),
AMOUNT DECIMAL(7,2) NOT NULL
CHECK (AMOUNT > 0),
FOREIGN KEY (PLAYERNO) REFERENCES PLAYERS (PLAYERNO))
;

[Terra]

MySQL does not support constraints, and 'CHECK' is parsed but pretty much ignored for all SQL engines except for InnoDB...

We do not support InnoDB except for on Dedicated MMS systems - too much overhead for a shared environment...

http://dev.mysql.com/doc/refman/5.0/en/create-table.html
For other storage engines, MySQL Server parses and ignores the FOREIGN KEY and REFERENCES syntax in CREATE TABLE statements. The CHECK clause is parsed but ignored by all storage engines. See Section 1.7.5.4, “Foreign Keys”.

Try removing that first, as well as possibly the 'FOREIGN KEY ........ REFERENCES .....' definition...

Also, to toss a grenade, try renaming 'PAYMENT_DATE' to 'PMTD' to see if you may have tripped up on a reserved word...

[nomadsoul]

Didn't know that, thanks I will try your suggestions.
Thanks

[hobbes]

except for on Dedicated MMS systems Is that the same as the old MQS?

[Kevin]

MMS (Managed Mysql Server) is a dedicated MySQL only server that goes along with either a shared web hosting account or an MQS server. They were created for sites that are very heavy MySQL or for MQS systems where the load was too great to have both engines on the same system.

We can do InnoDB on an MQS as well as an MMS. The MMS is just the cheapest way to get it.