sql create table with condition that 2 columns cannot have equal values

Question

How can I write a condition in a create table statement to say 2 columns cannot be equal?

CREATE table Example(
    sendId integer,
    recieveId integer,
    **!sendId cannot equal recieveId**

);

score 2 · Accepted Answer · answered Aug 30 '18 at 10:41

2

Use a check constraint:

CREATE table Example(
    sendId integer,
    recieveId integer,
    constraint not_equal check (sendid <> recieveId)
);

As your columns allow null values, you might want to take care of that too:

CREATE table Example(
    sendId integer,
    recieveId integer,
    constraint not_equal check (coalesce(sendid,0) <> coalesce(recieveId,0))
);

That treats NULL as 0 maybe using a different value that could never occur might be more appropriate.

Depending on your DBMS product, you could also use the standard operator is distinct from

constraint not_equal check (sendid is distinct from receiveid)

answered Aug 30 '18 at 10:41

I'm still being allowed to insert equal values in my actual table even with the check condition added. Could it be because they are foreign keys? I am using a php page with a prepare statement and bind param to insert values. – user2892730 Aug 30 '18 at 10:48
@user2892730: [it certainly works](http://rextester.com/LJGJ28825) which DBMS product are you using? – Aug 30 '18 at 10:50
I'm using phpmyadmin – user2892730 Aug 30 '18 at 10:52
@user2892730: MySQL doesn't support check constraints. – Aug 30 '18 at 10:54
Darn so am I out of luck then? I wanted to write the condition in MYSQL but I can write it on my php code instead if that's the case. Thank you for the explanation regardless. It's appreciated. – user2892730 Aug 30 '18 at 10:56
@user2892730: you can always upgrade to Postgres – Aug 30 '18 at 10:57

score 1 · Answer 2 · answered Aug 30 '18 at 10:41

1

You would use a check constraint:

create table Example (
    sendId integer,
    receiveId integer,
    constraint chk_example_sendId_receiveId check (sendId <> receiveId)
);

answered Aug 30 '18 at 10:41

Gordon Linoff

score 0 · Answer 3 · answered Aug 30 '18 at 10:41

0

You need check constraint :

constraint chk_sendId_receiveId check (sendId <> receiveId)

answered Aug 30 '18 at 10:41

Yogesh Sharma

3 Answers3