| Lesson 6 | Rules for Database Table Rows |
| Objective | List the rules for table rows. |
A database row is a record containing related column values. In relational terminology it is a tuple. Before choosing columns, define the table's grain: what does one row represent? A customer table might contain one row per customer; an order-line table contains one row per product line on an order.
The three central row rules are one value per attribute, no duplicate tuples in a relation, and reliable identification by a key. SQL tables do not automatically enforce every property of mathematical relations: a table can be created without a primary key and can then contain duplicate rows. Implement the intended rules explicitly.
In the conventional first-normal-form design used in this course, a cell contains one value from its attribute's domain. Atomicity is relative to the model: a date can be one value even though functions extract its year and month. Modern databases also support structured types; their existence does not justify putting independently managed phone numbers in a comma-separated text field.
If a customer can have several phone numbers, model each customer-phone association as a separate row. Splitting a string during every search is possible, but makes validation, indexing, and uniqueness harder than storing the association directly.
A mathematical relation contains no duplicate tuples. In SQL, a primary key prevents duplicate key values; it does not automatically prevent duplicate business facts. Two customer rows with different generated IDs can still describe the same customer.
Define additional uniqueness rules only when the business supports them. Names are not generally unique, and even email addresses may be shared. If a customer number is unique and mandatory, enforce that rule with UNIQUE and NOT NULL. Do not infer a candidate key merely because the small sample currently has distinct values.
A candidate key uniquely identifies rows and is minimal: removing any of its columns would destroy that guarantee. Minimal does not mean that every candidate key has the same number of columns. Choose one candidate as the primary key; retain the others as alternate keys.
A composite key is appropriate when the row represents a combination. For a customer-phone association, (CustomerID, PhoneNumber) can identify the association without inventing a separate identifier. A stable generated identifier is another design option, but the business combination may still need a uniqueness constraint.
The following SQL illustrates the constraints. Adapt data types and naming conventions to your DBMS; the phone number is text, not a quantity used in arithmetic.
CREATE TABLE Customers (
CustomerID INTEGER PRIMARY KEY,
CustomerNumber VARCHAR(20) NOT NULL UNIQUE,
CustomerName VARCHAR(100) NOT NULL
);
CREATE TABLE CustomerPhones (
CustomerID INTEGER NOT NULL,
PhoneNumber VARCHAR(30) NOT NULL,
PRIMARY KEY (CustomerID, PhoneNumber),
FOREIGN KEY (CustomerID) REFERENCES Customers (CustomerID)
);
Customer 101 may have two different phone numbers, but the same customer-number pair cannot be inserted twice. Two customers can share a phone number because PhoneNumber alone is not declared unique. A phone row for a nonexistent customer is rejected by the enabled foreign key. Decide separately how phone numbers will be standardized before comparison.
| Rule | Implementation | Important limit |
|---|---|---|
| One value per modeled attribute | Use suitable columns and related tables for repeated facts | Atomicity depends on the chosen domain |
| No duplicate business association | Declare the appropriate composite or alternate key | A generated ID alone may allow duplicate facts |
| Reliable row identity | Choose a primary key from the candidate keys | SQL permits tables without one; the designer must decide |
A foreign key can reference an eligible unique key as well as a primary key. Joins do not require a declared foreign key, but a join condition that matches several unintended parent rows can multiply results. Constraints express the intended relationship and reject violations; the query still needs the correct join predicate.
Rows have no guaranteed presentation order. Neither insertion order nor a primary-key index guarantees the order of a query result. Use ORDER BY when order matters:
SELECT CustomerID, PhoneNumber
FROM CustomerPhones
ORDER BY CustomerID, PhoneNumber;
A row records facts at the table's chosen grain. It may describe an entity, an event, or an association. An application object may combine several rows and also provide methods. An object-relational mapper helps translate between these representations, but does not replace database constraints or make one object equivalent to one row in every design.
Both display rows and columns. Spreadsheets can provide validation, but a relational database provides schema-level constraints and transaction handling for shared data. These controls work only when correctly defined and enabled.
NOT NULL rejects SQL NULL, not every visually blank value. Whitespace and empty strings need deliberate validation; empty-string behavior also differs by DBMS. A required name may need both a nullability rule and a rule against blank text.
A data type defines representable values and operations. A domain[1] adds the attribute's business meaning. A string column can store both a color and a customer number, but those values belong to different business domains. Type checks alone cannot determine whether a string is a valid customer number.
Before finalizing a table, ask: What does one row mean? Which values identify it? Which facts may repeat? Which constraints reject invalid data? These questions connect the row rules to the entities and attributes exercise below.
Implementation reference: PostgreSQL constraint documentation. Exact constraint and NULL behavior should be checked for the DBMS used in your project.
Before moving on, click the link below to review entities and attributes from the course ER diagram:
Entities Attributes Exercise