用户自定义类型的约束问题

Take a look at this interesting article http://forge.mysql.com/wiki/Triggers#Emulating_Check_Constraints Emulating Check Constraints Let me firstly give a brief idea of what check constraints are, in some other databases it’s possible to use check constraints on a table to maintain data integrity and enforce rules on the type and range of data that is entered into them. For example you may have used other types of constraints with MySQL such as the Primary Key or Unique constraints which raise an error if a duplicate value is entered into a column. Check constraints are similar in that they are defined when creating the table, they differ however in the fact that the table creator can define how the constraint operates rather than being limited to the rules defined in the small set that already exists. Lets say for example a client has a requirement that all salaries should be greater than 10 and less than 100. Using a check constraint we can simply check this when the insert takes place. Oracle allows the use of check constraints and the table creation script for this would be like so. create table emps ( emp_id number, salary number check (salary between 10 and 100) ); If you tried to insert a value outside of this range the Oracle would raise an exception and stop the transaction taking place. As mentioned MySQL doesn’t support check constraints as standard, so if we want to use them we need to come up with a method of checking the values being inserted into the tables, we could do this after using an update statement but at that point we won’t know if the whole record should have been rejected or just the column which fails the check constraint check. What we need is a method of rejecting the record, or at least telling the user that a column is failing the constraint check at the point the value is inserted or updated in the table. The best way to do this is to use a trigger, however MySQL procedural language doesn’t currently support the independent raising of an error, prior to 5.0.10 it was also not possible to call SQL to artificially raise an error, but even that is not an acceptable solution because the error wouldn’t have been specific enough to tell the user what went wrong. However with the release of 5.0.15 MySQL now offers us a solution, previously when an error was raised only the error number and standard message were returned to the user, from version 5.0.15 MySQL now also returns the value which you tried to insert. So we can use this to our advantage to return a more meaningful error message from a trigger. To do this we need to create two things, a table to produce the error against and a procedure to insert into that table which will in turn raise an error. It would be a good idea to create these in a separate database so that they can be accessed by any database. The table definition is as follows. CREATE TABLE `Error` ( `ErrorGID` int(10) unsigned NOT NULL auto_increment, `Message` varchar(128) default NULL, `Created` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, PRIMARY KEY (`ErrorGID`), UNIQUE KEY `MessageIndex` (`Message`)) ENGINE=MEMORY DEFAULT CHARSET=latin1 ROW_FORMAT=FIXED COMMENT='The Fail() procedure writes to this table twice to force a constraint failure.' The important things to note about this table are the unique key in the Message column, this allows us to easily create the error and the fact the table uses the MEMORY engine. This means the table is stored in memory and not in the file system, using that we don’t need to clear the table down or worry about the table becoming full. Next we need to create a procedure to insert into this table to raise the unique key violation, this stage isn’t strictly necessary but makes the trigger code we will produce in a moment much more readable. It also means we can consistently call the same routine in any number of triggers (or even in Functions and Procedures). DELIMITER $$ DROP PROCEDURE IF EXISTS `Fail`$$ CREATE PROCEDURE `Fail`(_Message VARCHAR(128)) BEGIN INSERT INTO Error (Message) VALUES (_Message); INSERT INTO Error (Message) VALUES (_Message); END$$ DELIMITER ; As you can see the procedure accepts a parameter called _Message, this is then used as the Message column value in the inserts. By calling the same insert statement twice the unique_key constraint is violated on the error table, because MySQL reports the column value in the error message this _Message parameter is added to the error message. We can call this procedure from the command line to see this in action. mysql> call fail(’Salary must be over 10’); ERROR 1062 (23000): Duplicate entry ’Salary must be over 10’ for key 2 There are two things to note here, firstly we get our error message returned which is great as it means the user can clearly see why the error was raised. The second thing you may notice is that the error number and description point to a duplicate entry error, this isn’t ideal but for now is the only way we can raise the error. We now have the table and a procedure capable of raising an error safely without effecting any other part of the database. That’s one of the key things here, we could raise the error without using a specific table or the procedure but doing so won’t effect any other part of the database. We now need to add this to a trigger so that the error is raised when the constraint fails. We will use the salary example we mentioned earlier. I you have been using the pers schema you will have a table called emps, this table has a column called salary. Let’s create a trigger which will enforce a constraint on that column so that the value cannot be less than 10 and not higher than 100. DELIMITER $$ create trigger salary_check before insert on pers.emps for each row begin if new.salary < 10 or new.salary > 100 then call fail(’Salary not in allowed range’); end if; end $$ DELIMITER ; The trigger simply checks the value of the new.salary column, if it’s not within the parameters allowed a call to the fail routine is called. This will then stop the record being inserted into the table. Let’s look at that in action, first we can take a look to see what values are in our emps table. mysql> select * from emps; -------- ---------- --------- -------- -------- | emp_id | emp_name | dept_id | salary | bonus | -------- ---------- --------- -------- -------- | 0 | Barry | NULL | 0.00 | NULL | | 1 | Paul | 1 | 100.00 | 100.00 | | 2 | John | 2 | 200.00 | 100.00 | | 3 | Alan | 1 | 300.00 | 100.00 | -------- ---------- --------- -------- -------- 4 rows in set (0.00 sec) We can first test that records that have a valid salary are inserted correctly. mysql> insert into emps (emp_id,emp_name,dept_id,salary) -> values (4,’Sally’,1,90.00); Query OK, 1 row affected (0.06 sec) mysql> select * from emps; -------- ---------- --------- -------- -------- | emp_id | emp_name | dept_id | salary | bonus | -------- ---------- --------- -------- -------- | 0 | Barry | NULL | 0.00 | NULL | | 1 | Paul | 1 | 100.00 | 100.00 | | 2 | John | 2 | 200.00 | 100.00 | | 3 | Alan | 1 | 300.00 | 100.00 | | 4 | Sally | 1 | 90.00 | NULL | -------- ---------- --------- -------- -------- 5 rows in set (0.00 sec) The record was inserted into the table without a problem, now we can try and insert a record which breaks the constraint and see what happens. mysql> insert into emps (emp_id,emp_name,dept_id,salary) -> values (5,’Penny’,1,200.00); ERROR 1062 (23000): Duplicate entry ’Salary not in allowed range’ for key 2 This time the value wasn’t in the range we have specified and the trigger raised the error just as we wanted. This demonstrates how we can replicate check constraints in MySQL, there are however a couple of minor issues, firstly the error message while passing back a more readable format still isn’t the correct error message, this could have consequences when updating or inserting records in a procedure which uses error handling, in particular handling the 1062 Duplicate Entry error specifically. The second problem is that MySQL currently only allows one trigger type per table, so you need to add the code to existing triggers, you also need to deal with the check for both Inserts and Updates individually because the two trigger types are independent under MySQL. Having pointed out the limitations it’s only fair then to say that this is an extremely powerful method of implementing check constraints. Normally check constraints under other databases are fairly limited, checking a value in a range is about as complicated as they get, but using this trigger method we can make them very complex, the sky really is the limit. 【 在 yunmi (may) 的大作中提到: 】 : 要求数据类型为整数，且存储的值不超过30000，应该如何设置呢？