Relational Data, Tables, Rows, and Keys

What you will be able to do

• Distinguish a table, row, column, data type, schema, stored data, and query result by their roles.
• Choose a primary key that identifies each row and explain why duplicate or missing key values are rejected.
• Trace a foreign key from a child row to a valid parent row and recognize an orphaned reference.
• Select constraints and table boundaries that preserve the intended meaning of a small relational design.

01

Think in Related Sets

A relational database stores facts in named relations, commonly presented as tables. Each table describes one kind of subject, such as customers, devices, tickets, or payments. The table gives related facts a shared structure instead of mixing them into an unstructured list.

A table is not merely a spreadsheet saved on a server. Its schema defines named columns, permitted data types, and rules that the database can enforce. Applications can then read and change rows through SQL while the database protects the declared structure.

Good relational design starts with meaning. Name the subject represented by one row, decide which facts belong to that subject, and separate facts whose lifecycle or ownership differs. This boundary makes later filtering, joining, and validation easier to reason about.

02

Read Rows and Columns

A row records one occurrence of the table's subject. In a customers table, one row might represent one customer. In a support_tickets table, one row might represent one reported issue.

Every row follows the same set of named columns. Values differ from row to row, while the column meanings remain consistent across the table.

A column describes one attribute shared by those rows. customer_name, opened_at, and priority express different meanings and should use data types suited to those meanings. A date column and a free-text column are not interchangeable merely because both can be displayed as characters.

Physical display order does not identify a row. SQL does not guarantee row order unless a query requests an order. A stable identifier, not a row's current screen position, must be used when another operation needs that exact row.

03

Treat the Schema as a Contract

The schema is the structural agreement for the table: column names, data types, default behavior, and constraints. Stored data is the current set of rows that follows that agreement. A query is a request that produces a result from the stored data.

Keep these layers separate when diagnosing a problem. A missing column is a schema issue, while a misspelled customer name is a data issue.

A report that filters the wrong status is a query issue. Each problem requires a different correction and a different verification step.

A query result can look like a table without becoming a new stored table. It may contain selected columns, calculated values, or rows combined from several sources. The result answers a question; the schema still defines the underlying records and their rules.

04

Identify Every Row with a Key

A primary key identifies each row in a table. Its value must be unique among the table's rows and cannot be null. A table has one declared primary key, although that key may combine two or more columns when the combination represents the identity.

Choose a key whose job is identity, not presentation. A ticket number is usually safer than a title because titles can repeat or change. A generated customer identifier is usually safer than an email address when the business permits addresses to change.

Other columns may also require unique values, but they do not become the primary key automatically. A unique constraint protects a candidate value from duplicates. The primary key remains the table's designated identity for references and row-specific changes.

05

Connect Tables with Foreign Keys

A foreign key stores a value that refers to a valid key in another table. The table containing the reference is the child or referencing table. The table that owns the matched key is the parent or referenced table.

Suppose customers uses customer_id as its primary key and tickets includes customer_id as a foreign key. Ticket 410 can refer to customer 27 without copying the customer's name and contact details into every ticket. The relationship remains explicit through matching values.

The foreign key protects referential integrity. A non-null child value must match an allowed parent key, so a ticket cannot silently point to customer 999 when that customer does not exist. An optional relationship may permit null when the schema deliberately allows no parent yet.

06

Use Constraints to Protect Meaning

Constraints turn important data rules into database checks. NOT NULL requires a value, UNIQUE prevents repeated values where uniqueness matters, CHECK tests a condition on a row, and FOREIGN KEY protects a relationship between tables.

Choose a constraint that matches the rule. A required ticket title needs NOT NULL, while a public ticket code may need UNIQUE.

A priority limited to known values can use CHECK. A customer reference belongs in a foreign key rather than an informal comment.

Application validation can give friendly feedback, but it is not a substitute for database protection. Several applications, imports, or administrator tools may write to the same tables. Database constraints give those paths one enforceable boundary for critical rules.

07

Separate Subjects Without Losing Links

Repeated groups of facts often reveal that two subjects were combined. If every ticket row repeats a customer's name, email, and department, one customer change must be copied to many rows. Missed copies create contradictory data.

Separate customers from tickets when they have different identities and lifecycles. Keep customer facts in customers, ticket facts in tickets, and connect them through customer_id. This does not remove the relationship; it represents the relationship without duplicating every parent attribute.

Do not split tables mechanically. Two columns belong together when they describe the same row subject and share its lifecycle. The design goal is a clear fact boundary, stable identity, and relationships that the database can validate.

08

Model a Small Support Database

A small support system needs technicians, customers, and tickets. Give each table its own primary key: technician_id, customer_id, and ticket_id. Store ticket status and opened_at with the ticket because they describe the ticket's state and history.

Place customer_id and assigned_technician_id on tickets as foreign keys. The customer reference may be required, while the technician reference may be null until assignment. Those choices express real workflow rules rather than accidental blanks.

Verify the model with concrete cases. Two tickets may share one customer, two technicians may have the same display name, and an unassigned ticket may exist. A ticket with a nonexistent customer should fail, while every accepted row should remain identifiable by its key.

09

Recap Before Practice and Prove

A relational table represents one kind of subject through rows that share named, typed columns. Meaningful table boundaries keep unrelated facts from becoming one fragile record.

A row represents one occurrence, while a column represents one attribute. Display position does not identify a row because query order must be requested explicitly.

A primary key provides the table's designated row identity through unique, non-null values. Other business values can use unique constraints without becoming the primary key.

A foreign key connects a child row to an allowed parent key. This relationship prevents non-null references to missing parent rows and preserves referential integrity.

Constraints protect required values, uniqueness, valid conditions, and relationships. Separate schema, stored data, and query logic so each defect is corrected at the proper layer.