Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

If I have a table that looks like this

CREATE TABLE foo (
   id INT NOT NULL AUTO_INCREMENT,
   aa INT NOT NULL,
   bb INT NOT NULL,
   PRIMARY KEY (id),
   UNIQUE KEY (aa, bb),
   CONSTRAINT aa_ref FOREIGN KEY (aa) REFERENCES bar (id),
   CONSTRAINT bb_ref FOREIGN KEY (bb) REFERENCES bar (id)
)

Is there a way to make sure that aa != bb besides using application level logic or forcing a trigger to fail on BEFORE INSERT?

share|improve this question

3 Answers 3

up vote 1 down vote accepted

MySQL does not support CHECK contrsaints directly, though if you have a recent enough version it supports triggers and error raising via SIGNAL, so you could define BEFORE INSERT and BEFORE UPDATE triggers that check the data and raise errors if the intended constraint is not satisfied.

This will be less efficient than native support for check constraints of course, so if you are performing a high volume of writes to that structure be sure to analys the performance difference caused by the trigger in case it harms your application too much.

share|improve this answer

No, you can't. In most DBMS (Postgres, SQL-Server, Oracle, DB2 and many others), you can just add a CHECK constraint:

ALTER TABLE foo 
  ADD CONSTRAINT aa_cannot_be_equal_to_bb_CHK
    CHECK (aa <> bb) ;

I don't see any way to have this in MySQL, using only referential constraints. Besides triggers, you could allow the two columns to have equal values and simply ignore the rows by accessing the table always through a view:

CREATE VIEW foo_correct AS
SELECT id, aa, bb
FROM foo
WHERE aa <> bb ;

Alternatively, you can restrict Insert and Update operations through (stored) procedures that take care of the constraint and not allow data to be inserted (or changed) that do not satisfy it.

share|improve this answer

The both of columns in question reference to the same Bar table. Could you split the Bar table into two so they contain id with the different sets of values?

share|improve this answer
    
Then, say we have three couples in the Foo table: (1,2) (2,3) (3,1). How should we split the three values? –  ypercube Apr 13 '13 at 20:22
    
I agree, mistaken suggestion. –  msi77 May 21 '13 at 9:43

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.