current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

I want create query in MySQL similiar to PostgreSQL below:

CREATE FUNCTION check_unique_pair(IN id1 INTEGER, IN id2 INTEGER) RETURNS INTEGER AS   $body$
DECLARE retval INTEGER DEFAULT 0;
BEGIN 
 SELECT COUNT(*) INTO retval FROM (
  SELECT * FROM edges WHERE a = id1 AND b = id2
  UNION ALL
  SELECT * FROM edges WHERE a = id2 AND b = id1
)  AS pairs;
RETURN retval;
END
$body$
LANGUAGE 'plpgsql';

ALTER TABLE edges ADD CONSTRAINT unique_pair CHECK (check_unique_pair(a, b) < 1);

I am new to MySQL.

share|improve this question
1  
What have you tried? –  ThiefMaster♦ Nov 6 '12 at 9:26
1  
try describing your table structure and what you really want to do using this function. Additionally you can check phpmyadmin and MySQL Workbench where you can perform SQL operations graphically. –  Chaitanya Nov 6 '12 at 9:34
    
This looks like a (buggy and unreliable) attempt to implement an exclusion constraint (postgresql.org/docs/current/static/…). Your implementation is extremely unlikely to actually work in the face of any concurrency; it should be urgently replaced with a PostgreSQL 9.1+ exclusion constraint. –  Craig Ringer Nov 6 '12 at 9:46
2  
@CraigRinger: that can be achieved with a simple unique index: create unique index idx_unique_pair on edges (least(a,b), greatest(a,b)). That also will be a lot more efficient (but that's not possible in MySQL). –  a_horse_with_no_name Nov 6 '12 at 9:49
    
@a_horse_with_no_name Nice trick. Sometimes the real talent is seeing the simplest possible answer - something you seem to be awfully good at. Also, wow, MySQL doesn't support expression indexes? –  Craig Ringer Nov 6 '12 at 9:50

2 Answers 2

up vote 3 down vote

The query can be simplified to:

SELECT COUNT(*)
FROM edges
WHERE a IN (id1, id2)
AND b in (id1, id2)
AND a <> b
   ;

, which can probably use a composite index on {a,b}

share|improve this answer
    
That won't provide a constraint, though. For that they'll need a trigger to check that only one row is returned from the (greatly improved) query. –  Craig Ringer Nov 6 '12 at 23:25
    
Embedding it into a trigger function is left as an exercise to the reader. Besides: The question is tagged both mysql and postgres, and the trigger syntax may differ. In postgres the whole thing could possibly be implemented by a foreign key constraint + unique{a,b} + CHECK (a<b) and/or a rewrite rule if a happens to be > b. –  joop Nov 8 '12 at 11:36
    
Yep, that's what I thought, I just wanted to make sure it was clearly stated. –  Craig Ringer Nov 8 '12 at 12:20
up vote 1 down vote

You appear to be attempting to implement an ASSERTION, table-level CHECK constraint, or an exclusion constraint, though the implementation shown won't work reliably.

MySQL doesn't support CREATE ASSERTION; neither does any other current RDBMS, as far as I know. Nor is subquery support in CHECK for table-level assertions generally supported.

You may need to emulate the behaviour you want using a trigger. See:

@a_horse_with_no_name points out in the comments that you can achieve this with a composite unique expression index. MySQL doesn't support expression indexes, so as noted in the comment it won't work in MySQL. Keep an eye on that bug.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.