Column Constraints and Defaults

Column Constraints and Defaults

Column constraints in ACME are based on the business rules and design principles discussed earlier in this appendix. For example, invoice number and invoice date are undefined until the order is invoiced; canceled date makes sense for canceled orders only, so fields ORDHDR_INVOICENBR_N, ORDHDR_INVOICEDATE_D, and ORDHDR_CANCELDATE_D in ORDER_HEADER table allow null values. ACME defines only two valid values for freight terms, prepaid (PPD) and collect (COL), so there is a check constraint on the SHIPMENT_FRTTERMS_S column of the SHIPMENT table. Most tables have referential integrity constraints to enforce parent/child relationships. Every table has a meaningless primary key, that is, each record in every table has a unique number hidden from nontechnical users that is used only by database programmers for table joins and other purposes.

Some columns have default values. For example, the value Y in the ORDER_HEADER.ORDHDR_READYTOINVOICE_S column that indicates that the order has been finalized, shipped, and signed off by the sales department, so it is ready to be invoiced. The default value for this column is N, so when a new order is created, it is populated automatically.