Home » Open Source » MySQL » Alter Table with check constraint (Oracle SQL developer)
Alter Table with check constraint [message #568514] Fri, 12 October 2012 20:39 Go to next message
etoilethay
Messages: 9
Registered: October 2012
Junior Member
Write an ALTER TABLE statement that adds two new check constraints to the Invoices table of the AP database. The first should allow (1) PaymentDate to be null only if PaymentTotal is zero and (2) PaymentDate to be not null only if PaymentTotal is greater than zero. The second constraint should prevent the sum of PaymentTotal and CreditTotal from being greater than InvoiceTotal.

Here is my code:
ALTER TABLE invoices ADD CONSTRAINT payment_date_ck CHECK ( ((payment_date IS NULL) AND (payment_total = 0))OR((payment_date IS NOT NULL) AND(payment_total>0)))

and the second one is
ALTER TABLE invoices ADD CONSTRAINT payment_date_ck CHECK (
ADD CONSTRAINT invoice_total_ck CHECK((payment_total+credit_total)<=invoice_total)
);

is there a way to have both constraints in one statement?

[Updated on: Fri, 12 October 2012 20:57]

Report message to a moderator

Re: Alter Table with check constraint [message #568519 is a reply to message #568514] Sat, 13 October 2012 01:47 Go to previous messageGo to next message
John Watson
Messages: 4570
Registered: January 2010
Location: Global Village
Senior Member
Welcome to the forum. Please read our OraFAQ Forum Guide and How to use [code] tags and make your code easier to read

Without the CREATE TABLE statement, it is impossible to test. But won't this do:
ALTER TABLE invoices
  ADD CONSTRAINT payment_date_ck CHECK ( ((payment_date IS NULL) AND (
  payment_total = 0))OR((payment_date IS NOT NULL) AND(payment_total>0)) AND ((
  payment_total+credit_total)<=invoice_total)) 


--
Edit: I think you may have a problem with nulls in the last condition, see this:
orcl> select ename,sal,comm,sal+comm from emp;

ENAME             SAL       COMM   SAL+COMM
---------- ---------- ---------- ----------
SMITH             800
ALLEN            1600        300       1900
WARD             1250        500       1750
JONES            2975
MARTIN           1250       1400       2650
BLAKE            2850
CLARK            2450
SCOTT            3000
KING             5000
TURNER           1500          0       1500
ADAMS            1100
JAMES             950
FORD             3000
MILLER           1300

14 rows selected.

orcl>
I'm sure you can fix it, perhaps with the NVL function

[Updated on: Sat, 13 October 2012 01:52]

Report message to a moderator

Re: Alter Table with check constraint [message #568523 is a reply to message #568519] Sat, 13 October 2012 02:19 Go to previous messageGo to next message
Michel Cadot
Messages: 59149
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Depending on your MySQL version you may not have NVL function but you have IFNULL or COALESCE.

Regards
Michel
Re: Alter Table with check constraint [message #568526 is a reply to message #568523] Sat, 13 October 2012 02:44 Go to previous message
John Watson
Messages: 4570
Registered: January 2010
Location: Global Village
Senior Member
Oh, golly! This is MySQL! Please ignore my post, I have no idea what I am talking about.
Previous Topic: MySQL data model
Next Topic: Raise Exception
Goto Forum:
  


Current Time: Sun Sep 21 17:54:53 CDT 2014

Total time taken to generate the page: 0.07565 seconds