A NOT NULL constraint requires a database column to contain a value instead of SQL NULL. It protects fields that must exist for a record to be meaningful. section 5.1
In an ACH exceptions table, every exception must identify its file, record number, and exception code, so those columns are declared NOT NULL. Optional record-type references remain nullable because an exception may involve only one record type. section 8.4.1
Use NOT NULL when:
- A value is required for every row, such as an exception's file identifier or record number. section 8.4.1
- A field should not be confused with an optional relationship; nullable foreign keys can represent information that applies only in some cases. section 8.4.1
Required columns in an ACH exceptions table
CREATE TABLE ach_exceptions (\n ach_exceptions_id UUID PRIMARY KEY,\n ach_files_id UUID NOT NULL,\n record_number NUMERIC NOT NULL,\n exception_code VARCHAR(3) NOT NULL\n); section 8.4.1
Listing 8.3 Creating the exceptions table
CREATE TABLE ach_exceptions (
ach_exceptions_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
ach_files_id UUID NOT NULL
REFERENCES ach_files(ach_files_id)
ON DELETE CASCADE ON UPDATE CASCADE,
ach_records_type_5_id UUID #1
REFERENCES ach_records_type_5(ach_records_type_5_id) #1
ON DELETE CASCADE ON UPDATE CASCADE, #1
ach_records_type_6_id UUID REFERENCES #1
ach_records_type_6(ach_records_type_6_id) #1
ON DELETE CASCADE ON UPDATE CASCADE, #1
record_number NUMERIC NOT NULL, #1
exception_code VARCHAR(3) NOT NULL REFERENCES #2
ach_exception_codes(exception_code) #2
ON UPDATE CASCADE #2
);
Inspect the complete exceptions-table definition, including required fields, nullable foreign keys, and cascading relationships. section 8.4.1
