CODEWITHSUNDEEP
mysql
Synesso
Synesso

Reputation: 38978

MySql constraint that two columns have different values on each row

How can I declare a constraint such that two columns may not have the same value in any row.

E.g. Given

create table `presents` (
    `from` varchar(255) NOT NULL,
    `to` varchar(255) NOT NULL
)

I want to make sure that nobody ever gives a present to themselves.

'alice', 'bob'     # OK
'bob',   'charlie' # OK
'bob',   'alice'   # OK
'bob',   'alice'   # OK again
'bob',   'bob'     # Fails

Upvotes: 1

Views: 411

Answers (2)

Plexar
Plexar

Reputation: 110

create table `presents` (
    `from` varchar(255) NOT NULL,
    `to` varchar(255) NOT NULL,
    CHECK(from<>to)
)

Upvotes: 3

Strawberry
Strawberry

Reputation: 33945

Here's one idea:

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table (id SERIAL PRIMARY KEY, `from` INT NOT NULL, `to` INT NOT NULL);

INSERT INTO my_table (`from`,`to`) SELECT 1,2 FROM (SELECT 1) x WHERE 1 <> 2;
INSERT INTO my_table (`from`,`to`) SELECT 1,1 FROM (SELECT 1) x WHERE 1 <> 1;


SELECT * FROM my_table;
+----+------+----+
| id | from | to |
+----+------+----+
|  1 |    1 |  2 |
+----+------+----+

Upvotes: 1

Related Questions