But if you follow Partha's approach, you will need to make sure that the
only inserts to the table occur via the view. Nothing in the definition of
the view itself prevents a user with the necessary authority from inserting
directly into the table. You'll need to ensure that your GRANTs don't permit
any person or program to insert data directly via the table.
Rhino
----- Original Message -----
From: "Partha Dutta" <[EMAIL PROTECTED]>
To: "'Michael Kruckenberg'" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Cc: <mysql@lists.mysql.com>
Sent: Saturday, June 25, 2005 11:11 AM
Subject: RE: CHECK constraint
> 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]
>
>
> --
> No virus found in this incoming message.
> Checked by AVG Anti-Virus.
> Version: 7.0.323 / Virus Database: 267.7.11/26 - Release Date: 22/06/2005
>
>
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.323 / Virus Database: 267.7.11/26 - Release Date: 22/06/2005
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
