CREATE Related Tables

Relational databases allow us to connect tables using relationships, making data management more efficient. In this lesson, you’ll learn how to create tables with relationships, including one-to-many and many-to-many relationships.

One-to-Many Relationships

A one-to-many relationship exists when one record in Table A is related to multiple records in Table B. For example, consider a database tracking puppies and their owners:

• Each puppy is owned by one person.
• Each person can own multiple puppies.

Example Schema

To establish this relationship, the puppies table includes:

• A primary key (id) to uniquely identify each puppy.
• A foreign key (owner_id) referencing the id column in the owners table.

owners table:

CREATE TABLE owners (
  id INTEGER PRIMARY KEY,
  name VARCHAR(50) NOT NULL
);
    

puppies table:

CREATE TABLE puppies (
  id INTEGER PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  age_yrs NUMERIC(3,1),
  breed VARCHAR(100),
  weight_lbs INTEGER,
  microchipped BOOLEAN DEFAULT 0,
  owner_id INTEGER,
  FOREIGN KEY (owner_id) REFERENCES owners(id)
);
    

Enable Foreign Key Constraints

In SQLite3, you must enable foreign key constraints to enforce relationships:

PRAGMA foreign_keys = ON;

This ensures that an error is thrown if a foreign key constraint is violated. For example:

• If a puppy’s owner_id does not exist in the owners table, an error will be thrown.
• If foreign key constraints are not enabled, no error will occur, but the data integrity will be compromised.

View Schema

After creating the tables, use the following command in SQLite3 to view the schema:

.schema

Many-to-Many Relationships

A many-to-many relationship exists when multiple records in Table A are associated with multiple records in Table B. For example:

• Each person can support multiple elephants at the zoo.
• Each elephant can be supported by multiple people.

Example Schema

To establish this relationship, use a join table called people_elephants. The join table includes:

• Two foreign keys: person_id (referencing the id column in the people table) and elephant_id (referencing the id column in the elephants table).

people table:

CREATE TABLE people (
  id INTEGER PRIMARY KEY,
  name VARCHAR(50) NOT NULL
);
    

elephants table:

CREATE TABLE elephants (
  id INTEGER PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  gender VARCHAR(6) NOT NULL,
  age INTEGER
);
    

people_elephants join table:

CREATE TABLE people_elephants (
  person_id INTEGER NOT NULL,
  elephant_id INTEGER NOT NULL,
  FOREIGN KEY (person_id) REFERENCES people(id),
  FOREIGN KEY (elephant_id) REFERENCES elephants(id)
);
    

Note: The join table does not require a primary key because it is primarily used for queries involving person_id or elephant_id.

Enable Foreign Key Constraints

As with the one-to-many relationship, you must enable foreign key constraints:

PRAGMA foreign_keys = ON;

View Schema

Use the .schema command to verify the structure of the tables and their relationships.

What You Learned

In this lesson, you learned how to create tables with relationships in a relational database:

• One-to-Many Relationships: Established with a foreign key in one table pointing to the primary key of another table.
• Many-to-Many Relationships: Implemented using a join table with foreign keys referencing the primary keys of the related tables.

Real-World Applications

Relational databases with foreign key constraints are widely used in systems such as:

• Animal shelters: Tracking owners and their pets.
• E-commerce platforms: Managing customers, orders, and products.
• Zoos: Linking sponsors and animals they support.

By understanding how to create relationships between tables, you can design efficient and normalized databases for any application.