3

Here is a very basic table to illustrate my question.

CREATE TABLE Customer (
CustID         INT,
CustLastName   VARCHAR (20),
ReferralID     INT,
ADD CONSTRAINT PRIMARY KEY (CustID),
ADD CONSTRAINT FOREIGN KEY (ReferralID) REFERENCES Customer(CustID)
);

My current code ensures that any only former customers who have CustID's can be in the ReferralID column (i.e. they told the customer about the store.) However, the problem is nothing is stopping CustID from equaling ReferralID in the same row, which is obviously impossible. A customer cannot tell themself about the store.

Basically, how do I stop CustID and ReferralID from having the same value in the same row?

Thank you, Andrew

Andrew Smith
  • 47
  • 1
  • 4

1 Answers1

1

To do this you would want a CHECK constraint. However MySQL hasn't implemented CHECK constraints yet, so you can use a trigger instead.

Related

Community
  • 1
  • 1
Mark Byers
  • 811,555
  • 193
  • 1,581
  • 1,452
  • ...although deceptively, check constraints are allowed to be coded (for compatibility with other databases) - they are just ignored!? – Bohemian Sep 20 '12 at 03:25
  • Yes. From the manual: "The CHECK clause is parsed **but ignored** by all storage engines." – Mark Byers Sep 20 '12 at 03:28
  • Hmm. I have no experience with triggers, (just started this class) but I will try to figure out something. Thanks for pointing me in the right direction. – Andrew Smith Sep 20 '12 at 03:40