A NOT NULL constraint requires every row to contain a value in one column. Use it for information a record cannot reasonably lack, such as a shipment identifier, its related order, shipping cost, method, tracking number, and shipment date. A column can remain nullable when the information is genuinely optional, such as an author’s middle name. section 19.1.1
When deciding whether a column needs NOT NULL, ask whether the row still makes sense without that value:
- Require it for identifiers and required relationships. ShipmentID cannot be null as a primary key, and OrderID is required because each shipment belongs to an order. section 19.1.1
- Require it for facts needed to describe every record, such as the cost, method, tracking number, and date of a shipment. section 19.1.1
- Leave it nullable only when some valid rows naturally have no value, such as authors without middle names. section 19.1.1
- Use it to enforce required payment data: every author payment needs an AuthorID, TitleID, and PaymentYear. section 23.1.2
CREATE TABLE shipment (
ShipmentID int NOT NULL,
OrderID int NOT NULL,
ShipmentCost decimal(5,2) NOT NULL,
ShipmentMethod char(1) NOT NULL,
TrackingNumber varchar(20) NOT NULL,
ShipmentDate datetime NOT NULL,
CONSTRAINT PK_shipment PRIMARY KEY (ShipmentID),
CONSTRAINT FK_shipment_orderheader FOREIGN KEY (OrderID)
REFERENCES orderheader(OrderID)
);
This CREATE TABLE definition places NOT NULL immediately after each column’s data type. The primary key and foreign key constraints remain separate table constraints. section 19.1.1
NOT NULL and DEFAULT solve different problems and can be used together. A DEFAULT supplies a value when an INSERT omits the column, while NOT NULL rejects an explicitly supplied NULL. For example, ShipmentDate can require a value and default to the current date when none is provided. section 19.1.2
Required author-payment fields
An author-payment row is incomplete without the author receiving payment, the title being paid for, or the year of payment. Rename vague columns to AuthorID and TitleID, link them with FOREIGN KEY constraints, and make AuthorID, TitleID, and PaymentYear NOT NULL. A CHECK constraint may additionally limit PaymentYear to an acceptable range, but it does a different job from requiring a value. section 23.1.2
A NOT NULL constraint protects data integrity by preventing missing values; it does not make values unique, check a range, or supply a value by itself. SQL products can differ in constraint syntax—for example, MySQL requires parentheses around CURRENT_DATE() in the illustrated default expression—so verify product-specific details when writing a CREATE TABLE statement. section 19.1.2 section 19.3.2
