0

I have a simple MYSQL table with following columns:

first | second

first and second are integers. The primary key for the table is

PRIMARY KEY  (`first`,`second`)

So this allows only a unique combination of values like:

first | second
  1   |   2
  2   |   1

But this key also accepts the same value for both columns. For example:

first | second
  1   |   1

Is there a way to force both values to be different using MYSQL. I can do a check with PHP before inserting into the database but I'm wondering if there is a way in MYSQL to achieve it?

Michael Samuel
  • 3,820
  • 12
  • 45
  • 85

1 Answers1

2

This restriction can't be enforced by a PRIMARY KEY or UNIQUE constraint.

Unfortunately, MySQL does not enforce CHECK CONSTRAINTS, which is what we would likely use in other databases.

To get MySQL to enforce a constraint like this, you would need to implement a BEFORE INSERT and a BEFORE UPDATE trigger.

The "trick" in the trigger body would be to detect this condition you want to restrict, e.g.

 IF (NEW.first = NEW.second) THEN

And then have the trigger throw an error. In more recent versions of MySQL provide the SIGNAL statement for raising an exception. In older versions of MySQL, you'd run a statement that would throw an error (for example, performing a SELECT against a table name that is known not to exist.)

FOLLOWUP

The IF statement is valid only within the context of a MySQL stored program (for example, a PROCEDURE, FUNCTION, or TRIGGER).

To get this kind of restriction applied by an INSERT statement itself, without a constraint or trigger, we'd need to use the INSERT ... SELECT form of an INSERT statement.

For example:

INSERT INTO `mytable` (`first`, `second`)
SELECT t.first, t.second
  FROM ( SELECT '1' AS `first, '1' AS `second`) t
 WHERE t.first <> t.second

Since the SELECT statement returns no rows, no rows are inserted to the table.

Note that this approach applies the restriction only on this statement; This doesn't prevent some other session from performing an INSERT that doesn't enforce this restriction. To get this restriction enforced as a constraint "by the database", you'd need to implement a BEFORE INSERT and BEFORE UPDATE trigger I described earlier in the answer.

spencer7593
  • 106,611
  • 15
  • 112
  • 140
  • can you please explain how to do this with an insert command? `IF (1.first <> 1.second) THEN INSERT INTO table (first,second) VALUES ('1','2')` I tried this query but it's giving me a syntax error – Michael Samuel Sep 25 '14 at 14:04
  • @MichaelSamuel: Of course that's giving you a syntax error. **`IF`** is *not* a valid SQL statement in MySQL; that statement is valid only in the context of a MySQL stored program (i.e. a procedure, function or trigger); apparently, I didn't make that point clear enough in my answer. To prevent inserting rows within a MySQL INSERT statement, you'd need to use an `INSERT ... SELECT` form of the INSERT statement. – spencer7593 Sep 25 '14 at 15:19