| Alter Table with check constraint [message #568514] |
Fri, 12 October 2012 20:39  |
 |
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  |
John Watson
Messages: 8963
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>
[Updated on: Sat, 13 October 2012 01:52] Report message to a moderator |
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
