Another approach would be to use a view with a CHECK OPTION. This will
allow the view to behave exactly like a check constraint:
CREATE VIEW tblJob_view AS
SELECT JobId, CustomerId, JobType, Description,
QuotationDate, OrderDate
FROM tblJob
WHERE JobType IN ('DesignOnly', 'DesignInstall', 'InstallOnly')
WITH CHECK OPTION
This will force or constraint the view to only accept inserts where the
criteria specified in the WHERE clause matches.
--
Partha Dutta, Senior Consultant
MySQL Inc, NY, USA, www.mysql.com
Are you MySQL certified? www.mysql.com/certification
> -----Original Message-----
> From: Michael Kruckenberg [mailto:[EMAIL PROTECTED]
> Sent: Saturday, June 25, 2005 10:57 AM
> To: [EMAIL PROTECTED]
> Cc: mysql@lists.mysql.com
> Subject: Re: CHECK constraint
>
> A trigger is a good place to check the value, and change it, bit I
> don't believe you can actually generate a MySQL error within the
> trigger that will prevent the data from being inserted. Currently
> (unless there's been an update to triggers that's not yet in the
> docs), you can only change the value before it gets inserted.
>
> If you are looking to enforce the values going into your JobType
> column, you might be better off creating a JobType table, with a
> foreign key restraint between the tblJob.JobType and JobType.Name,
> and make sure that the only entries in the JobType.Name column are
> those you want to appear in the tblJob.JobType column.
>
> On Jun 25, 2005, at 10:28 AM, Chris Andrew wrote:
>
> > Dear List,
> >
> > My system is RedHat EL3 and MySQL 5.0.7-beta.
> >
> > I wanted to implement a check constraint (below), but after some
> > testing
> > and googling, it seems I can't do this with MySQL. I've read
> > suggestions
> > that check(s) should be done using triggers. Is a trigger a preferred
> > method of achieving the following:
> >
> > CREATE TABLE tblJob (
> > JobId SMALLINT UNSIGNED NOT NULL,
> > CustomerId SMALLINT UNSIGNED NOT NULL,
> > JobType VARCHAR(20) NOT NULL DEFAULT 'DesignInstall',
> > Description VARCHAR(100) NOT NULL,
> > QuotationDate DATE NOT NULL,
> > OrderDate DATE,
> > CHECK (JobType IN ('DesignOnly', 'DesignInstall', 'InstallOnly')),
> > PRIMARY KEY (JobId, CustomerId)
> > ) TYPE=InnoDB;
> >
> > Regards,
> > Chris
> >
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe: http://lists.mysql.com/mysql?
> > [EMAIL PROTECTED]
> >
>
> Mike Kruckenberg
> [EMAIL PROTECTED]
> "ProMySQL" Author
> http://www.amazon.com/exec/obidos/ASIN/159059505X
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
