I have a table containing an url and a string representing its parameters. The problem is I want an url and a parameterstring to be the unique constraint for the table - aka no entries can have the same url AND parameter string. The parameter string can be of arbitrary length (longer than 800bytes or so which is the max length for a MySql key, so I cant use Unique(url, params) since it throws an error...).
I thought about using triggers to do this, but how do I throw an exception/raise an error if the trigger discovers the insert is about to insert a duplicate entry? I imagine I would like to have a MySqlException thrown like MySql does with duplicate primary keys etc so I can catch it in my C# code.
I have two pieces in the trigger I need to get help with:
... Abort throw exception to C# ... How do I throw an exception etc to C#?
... Allow insert ... - how do I just allow the insert if there is no duplicate entry?
Heres the trigger code:
CREATE TRIGGER urls_check_duplicates
BEFORE INSERT ON urls
FOR EACH ROW
BEGIN
DECLARE num_rows INTEGER;
SELECT COUNT(*)
INTO num_rows
FROM urls
WHERE url = NEW.url AND params = NEW.params;
IF num_rows > 0 THEN
... ABORT/throw exception to C# ...
ELSE
... Allow insert ...
END
解决方案
The comments in the mysql documentation about triggers suggest that there is no such feature in mysql. The best you can do is to create a separate table for your trigger errors, as suggested on the same page.