VALIDATING SQL TABLE

Question

Hello guys, Please i need help with this table's validation
Table: Transactions.OrderDetails
1. PurchaseOrderID must be auto generated.
2. OrderDate should not be greater than the current date.
3. If the order date is not entered, the current date should be taken as the default date.
4. QuantityOrdered, QuantityReceived, and UnitPrice should be greater than 0.
5. QuantityReceived should allow NULL.
6. QuantityReceived cannot be greater than QuantityOrdered.
7. QuantityReceived should be added to QuantityInHand in the Items table.
8. When a record is inserted into the table, QuantityInHand in the Items table should be updated automatically.
9. OrderStatus must be any of the following values: ‘InTransit’, ‘Received’, or ‘Cancelled’.
10. ReceivingDate should allow NULL and should be greater than OrderDate.

Answer 1

I suggest you use phpMyAdmin as it will give you a graphical interface to manipulate every element of your table, autoindexing, default values ecc...
Although I think some of the input validation, being very specific, shold be carried out by the code that executes the SQL queries (for example php if you are using it).
This way you would also reduce the number of connections to the DB by executing the queries only when the input is correct.

Answer 2

First you should give proper validation at front end side. And if you want to give validation at back end side. You achieve this by creating trigger. And in your case..you should create BEFORE INSERT trigger