Re: CHECK constraint
Hi,
your enum canbe NULL and is not indexed. So you can insert values not in enum,
replaced by NULL.
a solution is to UNIQUE index the enum column, and insert a unique bad value in
it.
Any value not in enum can not then be inseted :
mysql create table enum_test(id int, name enum('test1','test2') NOT NULL,
UNIQUE KEY(name));
Query OK, 0 rows affected (0.06 sec)
mysql desc enum_test;
+---+---+--+-+-+---+
| Field | Type | Null | Key | Default | Extra |
+---+---+--+-+-+---+
| id| int(11) | YES | | NULL| |
| name | enum('test1','test2') | | PRI | test1 | |
+---+---+--+-+-+---+
2 rows in set (0.00 sec)
mysql INSERT INTO enum_test VALUES (0,'test3');
Query OK, 1 row affected, 1 warning (0.02 sec)
mysql INSERT INTO enum_test(id) VALUES (1);
Query OK, 1 row affected (0.01 sec)
mysql SELECT * from enum_test;
+--+---+
| id | name |
+--+---+
|0 | |
|1 | test1 |
+--+---+
2 rows in set (0.00 sec)
mysql INSERT INTO enum_test VALUES (1,'test3');
ERROR 1062 (23000): Duplicate entry '' for key 1
Mathias
Selon Michael Kruckenberg [EMAIL PROTECTED]:
Hi,
Use enum with a default type and let mysql do the check for you.
The problem with an enum is that if you insert a value that's not in
the enum, MySQL doesn't stop the insert, it leaves the column empty.
This doesn't enforce data integrity like I think Chris wanted.
mysql desc enum_test;
+---+---+--+-+-+---+
| Field | Type | Null | Key | Default | Extra |
+---+---+--+-+-+---+
| id| int(11) | YES | | NULL| |
| name | enum('test1','test2') | YES | | test2 | |
+---+---+--+-+-+---+
2 rows in set (0.25 sec)
mysql INSERT INTO enum_test VALUES (1,'test3');
Query OK, 1 row affected, 1 warning (0.29 sec)
mysql SELECT * from enum_test;
+--+--+
| id | name |
+--+--+
|1 | |
+--+--+
1 row in set (0.00 sec)
Mike Kruckenberg
[EMAIL PROTECTED]
ProMySQL Author
http://www.amazon.com/exec/obidos/ASIN/159059505X
Hope that helps
:o)
Mathias
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
CHECK constraint
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,
CustomerIdSMALLINT 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/[EMAIL PROTECTED]
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,
CustomerIdSMALLINT 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]
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,
CustomerIdSMALLINT 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]
Re: CHECK constraint
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,
CustomerIdSMALLINT 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]
Re: CHECK constraint
Selon Michael Kruckenberg [EMAIL PROTECTED]:
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,
CustomerIdSMALLINT 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]
Hi,
Use enum with a default type and let mysql do the check for you.
Hope that helps
:o)
Mathias
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CHECK constraint
Hi,
Use enum with a default type and let mysql do the check for you.
The problem with an enum is that if you insert a value that's not in
the enum, MySQL doesn't stop the insert, it leaves the column empty.
This doesn't enforce data integrity like I think Chris wanted.
mysql desc enum_test;
+---+---+--+-+-+---+
| Field | Type | Null | Key | Default | Extra |
+---+---+--+-+-+---+
| id| int(11) | YES | | NULL| |
| name | enum('test1','test2') | YES | | test2 | |
+---+---+--+-+-+---+
2 rows in set (0.25 sec)
mysql INSERT INTO enum_test VALUES (1,'test3');
Query OK, 1 row affected, 1 warning (0.29 sec)
mysql SELECT * from enum_test;
+--+--+
| id | name |
+--+--+
|1 | |
+--+--+
1 row in set (0.00 sec)
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]
question: check constraint
Mysql 4.0.12 on win xp. Question: is the check constraint supported? Example: create table temp ( c1 varchar (50) check (c1 like [EMAIL PROTECTED]) ); insert into temp values (whatever); will succeed. Is my constraint wrong (say to check the email address)? or this is just parsed without enforcement? Thanks __ Do you Yahoo!? Yahoo! Mail - More reliable, more storage, less spam http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: question: check constraint
At 20:10 -0800 3/13/04, Andrew Zhu wrote: Mysql 4.0.12 on win xp. Question: is the check constraint supported? Example: create table temp ( c1 varchar (50) check (c1 like [EMAIL PROTECTED]) ); insert into temp values (whatever); will succeed. Is my constraint wrong (say to check the email address)? or this is just parsed without enforcement? The latter: http://www.mysql.com/doc/en/CREATE_TABLE.html In MySQL Version 3.23.44 or later, InnoDB tables support checking of foreign key constraints. See section 15 The InnoDB Storage Engine. Note that the FOREIGN KEY syntax in InnoDB is more restrictive than the syntax presented above: The columns of the referenced table must always be explicitly named. InnoDB supports both ON DELETE and ON UPDATE actions on foreign keys as of MySQL 3.23.50 and 4.0.8, respectively. See the InnoDB manual section for the precise syntax. See section 15.6.4 FOREIGN KEY Constraints. For other storage engines, MySQL Server does parse the FOREIGN KEY and REFERENCES syntax in CREATE TABLE commands, but without further action being taken. The CHECK clause is parsed and ignored for all storage engines. See section 1.8.5.5 Foreign Keys. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com MySQL Users Conference: April 14-16, 2004 http://www.mysql.com/uc2004/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
CHECK constraint
I created a table as follows: mysql CREATE TABLE test ( - age INT(3), - CONSTRAINT CHECK (age 0) - ); Query OK, 0 rows affected (0.00 sec) I then inserted -1 into the table, which it shouldn't have let me do. mysql insert into test values (-1); Query OK, 1 row affected (0.00 sec) mysql select *From test; +--+ | age | +--+ | -1 | +--+ 1 row in set (0.01 sec) How can I enforce the CHECK constraint ? thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CHECK constraint
Hi, I created a table as follows: mysql CREATE TABLE test ( - age INT(3), - CONSTRAINT CHECK (age 0) - ); Query OK, 0 rows affected (0.00 sec) I then inserted -1 into the table, which it shouldn't have let me do. mysql insert into test values (-1); Query OK, 1 row affected (0.00 sec) mysql select *From test; +--+ | age | +--+ | -1 | +--+ 1 row in set (0.01 sec) How can I enforce the CHECK constraint ? MySQL doesn't support CHECK constraints: http://www.mysql.com/doc/en/CREATE_TABLE.html With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Check Constraint In Mysql
Hi !!! Is there a check constraint available in Mysql? If there is one plase send the Syntax as well Amit Lonkar __ Do You Yahoo!? Send your FREE holiday greetings online! http://greetings.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
CHECK CONSTRAINT
Hi, Any round about way of achieving CHECK CONSTRAINT FEATURE in MySQL before inserting a data into database? REgards, Ravi Do You Yahoo!? Get your free @yahoo.co.uk address at http://mail.yahoo.co.uk or your free @yahoo.ie address at http://mail.yahoo.ie - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
