Using DDL to Manage Tables and Their Relationships: Complete Guide

DDL statements define and change the structure of database objects — tables, views, indexes, sequences, and other schema elements. Four commands cover almost every case: CREATE brings a new object into existence, ALTER changes an existing one, DROP removes it permanently, and TRUNCATE empties a table while keeping its structure.

-- Create a new table
CREATE TABLE customers (
    customer_id   VARCHAR2(10) PRIMARY KEY,
    customer_name VARCHAR2(100) NOT NULL,
    region        VARCHAR2(20),
    credit_limit  NUMBER(12,2)
);

-- Change it — add a column
ALTER TABLE customers ADD loyalty_tier VARCHAR2(20);

-- Remove the column
ALTER TABLE customers DROP COLUMN loyalty_tier;

-- Drop the whole table
DROP TABLE customers;

The distinction from DML matters. DML changes data inside a table — insert, update, delete — and those changes can be rolled back until you commit. DDL changes the structure and is auto-committed by default. A DROP TABLE is irreversible. The only recovery is a backup.

You write a CREATE TABLE statement that lists the table name, every column with its data type, and any constraints on those columns. Once it runs, the table structure exists and waits for data.

CREATE TABLE customers (
    customer_id   VARCHAR2(10)  PRIMARY KEY,
    customer_name VARCHAR2(100) NOT NULL,
    region        VARCHAR2(20),
    credit_limit  NUMBER(12,2)
);

Four columns with different rules. The PRIMARY KEY on customer_id makes each row uniquely identifiable. NOT NULL on customer_name means every row must have a name. The other two columns are optional — they can be left NULL.

The equivalent structure in other databases uses slightly different type names. VARCHAR2 becomes VARCHAR in MySQL and SQL Server. NUMBER(12,2) becomes DECIMAL(12,2) or NUMERIC(12,2). The concept of a typed, constrained column is universal.

Once the table exists, you can add indexes for common queries, foreign keys pointing at other tables, and additional constraints. But CREATE TABLE itself only has to be run once — the structure persists until someone drops or alters it.

A primary key uniquely identifies each row in its table. A foreign key points at a primary key in another table, linking the two. Together they form the backbone of relational integrity.

CREATE TABLE customers (
    customer_id VARCHAR2(10) PRIMARY KEY,
    customer_name VARCHAR2(100) NOT NULL
);

CREATE TABLE orders (
    order_id    NUMBER(10) PRIMARY KEY,
    customer_id VARCHAR2(10),
    order_amount NUMBER(12,2),
    CONSTRAINT fk_orders_customer
        FOREIGN KEY (customer_id) REFERENCES customers (customer_id)
);

The customers table has customer_id as its primary key. The orders table has its own primary key, order_id, plus a foreign key on customer_id that points at the customers table.

The foreign key enforces a rule: every value in orders.customer_id must exist in customers.customer_id. Attempting to insert an order for a customer that does not exist fails immediately. That is what stops orphaned orders from accumulating in the database.

The same constraint protects deletes. Trying to delete a customer who still has orders fails unless you have specified ON DELETE CASCADE, which says the child rows should go with the parent. The safe default — plain foreign key — refuses the delete and forces you to think about it.

ALTER TABLE changes the structure of an existing table without recreating it. You can add columns, modify them, rename them, drop them, and add or remove constraints — all without losing the data that is already there.

-- Add a column
ALTER TABLE customers ADD contact_email VARCHAR2(100);

-- Modify a column's type
ALTER TABLE customers MODIFY credit_limit NUMBER(15,2);

-- Rename a column
ALTER TABLE customers RENAME COLUMN contact_email TO email;

-- Drop a column
ALTER TABLE customers DROP COLUMN email;

-- Add a constraint
ALTER TABLE customers ADD CONSTRAINT uq_customer_name UNIQUE (customer_name);

Each operation preserves the data that is not directly affected. Adding a column leaves existing rows untouched — the new column starts as NULL. Renaming a column does not change any values, only the name. Modifying a type converts the existing values to the new type if they are compatible.

The syntax differs slightly between databases. Oracle and MySQL use MODIFY for changing types. SQL Server and PostgreSQL use ALTER COLUMN. SQL Server uses a stored procedure (sp_rename) for renaming columns, not a DDL statement. Once you know the pattern, translating is straightforward.

You use ALTER TABLE with the ADD clause, name the new column, and give it a data type.

ALTER TABLE customers
ADD contact_email VARCHAR2(100);

Every existing customer row gets a new column called contact_email with a value of NULL. New inserts can provide a value or leave it unset.

If the column should be required, you need a default so existing rows can be filled in:

ALTER TABLE customers
ADD contact_email VARCHAR2(100)
    DEFAULT 'unknown@example.com'
    NOT NULL;

The DEFAULT supplies a value for every existing row. The NOT NULL then ensures future inserts also supply something — either an explicit value or the default.

One production consideration: adding a nullable column is usually instant because the database just updates its metadata. Adding a NOT NULL column with a default used to require a full table rewrite on older databases, though modern versions of most engines have optimised this. On a table with hundreds of millions of rows, it is worth checking the behaviour of your specific database before running the change during peak hours.

ALTER TABLE ... DROP COLUMN removes a column and every value in it, permanently.

ALTER TABLE customers DROP COLUMN contact_email;

The column is gone. Any code — queries, views, stored procedures, application logic — that references the old column name fails from that point on. The database does not track those references for you, so it is worth searching the codebase before dropping.

If a constraint or index depends on the column, the drop fails unless you also remove the dependent objects. Oracle allows a shortcut:

ALTER TABLE customers DROP COLUMN contact_email CASCADE CONSTRAINTS;

That drops the column and any constraints that referenced it, in one statement. The alternative is to drop the constraint first, then the column.

Unlike adding a column, dropping one is not easily reversible. Most databases do not keep a copy of the data. If you think you might need the column back, take a backup or copy the values to another table first. That safety net costs a few minutes; recovering a dropped column without one can cost much more.

Renaming a column is a DDL operation, but the syntax differs meaningfully between databases.

-- Oracle, PostgreSQL
ALTER TABLE customers RENAME COLUMN old_name TO new_name;

-- MySQL
ALTER TABLE customers
CHANGE COLUMN old_name new_name VARCHAR2(100);

-- SQL Server
EXEC sp_rename 'customers.old_name', 'new_name', 'COLUMN';

Oracle and PostgreSQL use a clean rename — only the two names appear. MySQL's CHANGE COLUMN requires the data type to be repeated, which is a legacy of how MySQL handles column modification generally. SQL Server uses a stored procedure rather than a DDL statement, which surprises people the first time they try it.

Renaming a column is one of the riskier schema changes in production. Every query that references the old name breaks the moment the rename commits — views, stored procedures, application code, reporting tools. Renaming a column is often more trouble than it is worth; the alternative is to add a new column with the desired name, migrate data into it, and drop the old one after everything has moved over.

If you do rename, deploy the schema change and the code change together. A window where one is updated and the other is not is exactly when the bugs happen.

You use ALTER TABLE with MODIFY (Oracle, MySQL) or ALTER COLUMN (SQL Server, PostgreSQL).

-- Oracle, MySQL
ALTER TABLE customers MODIFY credit_limit NUMBER(15,2);

-- SQL Server, PostgreSQL
ALTER TABLE customers ALTER COLUMN credit_limit DECIMAL(15,2);

That changes the column's type from NUMBER(12,2) to NUMBER(15,2) — widening it to allow larger values. The database checks every existing row to make sure the new type can hold its current value. If any row is incompatible, the change fails and nothing is modified.

Widening a numeric column usually succeeds because the values already fit. The reverse — narrowing the column — fails whenever an existing value would be truncated. That is a safety feature. The database refuses to silently lose data.

Changing a type between categories is harder. Converting a text column to a number works only if every existing value can be parsed as a number. If any row contains "abc", the change fails. Changing a date column to text is easier — every date can be represented as text.

In production, changing a column's type on a large table is often the slowest schema change you can make. Some databases rewrite the entire table to accommodate the new type. Plan these changes for maintenance windows, or use online-schema tools when the table cannot be locked.

DROP TABLE removes a table from the database permanently — structure, data, indexes, constraints, all of it.

DROP TABLE orders;

After that statement, the table is gone. Any query that referenced it fails immediately. The storage it occupied is freed. There is no undo.

If the table has foreign keys pointing at it from other tables, some databases refuse the drop unless you specify a cascade option:

-- MySQL, PostgreSQL
DROP TABLE orders CASCADE;

-- Oracle
DROP TABLE orders CASCADE CONSTRAINTS;

Both forms remove the foreign key constraints that point at the dropped table. The tables that owned those constraints are left intact — they simply no longer reference the dropped table.

The safe practice before dropping anything in production: confirm that no other table has a foreign key pointing at it (or accept that the constraints will go too), confirm that no code references it, and take a backup. A drop that nobody has thought through is one of the few DDL mistakes that cannot be fixed by dropping and recreating — the data is gone.

CASCADE tells the database to remove any objects that depend on the table being dropped — foreign keys from other tables, views that reference it, and sometimes indexes.

-- MySQL, PostgreSQL
DROP TABLE orders CASCADE;

-- Oracle
DROP TABLE orders CASCADE CONSTRAINTS;

Without the cascade option, the drop fails if any foreign key references the table. The database is protecting referential integrity — it does not want to leave a foreign key in another table pointing at a table that no longer exists.

With the cascade option, the drop succeeds and the referencing constraints are also removed. The other tables stay, but they are now independent of the dropped table — as if the constraint had never existed.

The wording differs between databases. MySQL and PostgreSQL use plain CASCADE, which removes dependent objects. Oracle uses CASCADE CONSTRAINTS, which removes only the constraints — it does not remove views or stored procedures that reference the table, unlike the other databases. SQL Server requires you to drop the constraints separately before dropping the table.

Reach for cascade when you know what depends on the table and you want them gone too. Avoid it when you are unsure — running the plain DROP TABLE first and reading the error message is a safer way to find out what depends on it.

Renaming a table is a DDL operation, and like renaming a column, the syntax varies between databases.

-- Oracle, PostgreSQL
RENAME employees TO staff;

-- MySQL
RENAME TABLE employees TO staff;

-- SQL Server
EXEC sp_rename 'employees', 'staff';

Oracle and PostgreSQL use RENAME old TO new directly. MySQL wraps it in RENAME TABLE. SQL Server uses a stored procedure, sp_rename, that takes the current name, the new name, and (optionally) the type of object being renamed.

The table and its data are untouched by the rename — only the name changes. Indexes, constraints, foreign keys, and triggers all stay attached to the table. Anything that references the table by its old name now fails until the reference is updated.

Foreign keys pointing at the renamed table keep working, because the foreign key references the table by its internal identifier, not by the name. Views that were defined as SELECT * FROM employees will need to be updated if they reference the old name explicitly — some databases resolve the view definition at creation time, some at execution time.

You add constraints with ADD CONSTRAINT, drop them with DROP CONSTRAINT, and — for anything else — you drop and re-add.

-- Add a unique constraint
ALTER TABLE customers
ADD CONSTRAINT uq_customer_name UNIQUE (customer_name);

-- Drop it
ALTER TABLE customers
DROP CONSTRAINT uq_customer_name;

-- Replace it with a different definition
ALTER TABLE customers
ADD CONSTRAINT uq_customer_name_v2 UNIQUE (customer_name, region);

Every constraint you add is validated against existing data. If the table already violates the rule — duplicate customer names, say — the ADD fails and nothing changes. Fix the data, then add the constraint.

Modifying a constraint is not usually a direct operation. Most databases do not support ALTER CONSTRAINT. The pattern is drop-then-add. On databases that support transactional DDL (PostgreSQL), both statements run inside a single transaction so there is no window where the constraint is missing. On Oracle and MySQL, the drop takes effect immediately, so a brief window exists where the constraint is not enforced.

That window matters on production systems. Between drop and add, inserts can go through that would have been rejected. If the constraint is doing real work, plan the change for low-traffic periods or wrap it in a way the application can handle.

ALTER TABLE with ADD CONSTRAINT and FOREIGN KEY creates the link between an existing column and the primary key of another table.

ALTER TABLE orders
ADD CONSTRAINT fk_orders_customer
    FOREIGN KEY (customer_id) REFERENCES customers (customer_id);

After that statement, orders.customer_id can only hold values that exist in customers.customer_id. Attempting to insert an order for a customer that does not exist fails.

The database checks every existing row when you add the constraint. If the table already contains orphaned references — orders pointing at customers that do not exist — the ADD fails. You have to fix the data first, which is usually a good audit in itself.

For cascading behaviour when the parent row is deleted, add an option:

ALTER TABLE orders
ADD CONSTRAINT fk_orders_customer
    FOREIGN KEY (customer_id) REFERENCES customers (customer_id)
    ON DELETE CASCADE;

That says: when a customer is deleted, also delete their orders. Useful when the child rows are genuinely dependent on the parent. Dangerous when the child rows have value of their own — you are one DELETE away from losing a lot of history.

Not every database supports every cascade option. Oracle, PostgreSQL, MySQL, and SQL Server all support ON DELETE CASCADE. Support for ON UPDATE CASCADE varies. Check the docs for the specifics of your database before relying on them.

You drop it with ALTER TABLE ... DROP CONSTRAINT, naming the constraint.

-- Oracle, PostgreSQL, SQL Server
ALTER TABLE orders DROP CONSTRAINT fk_orders_customer;

-- MySQL
ALTER TABLE orders DROP FOREIGN KEY fk_orders_customer;

MySQL uses DROP FOREIGN KEY specifically for foreign key constraints. Other constraint types use DROP CONSTRAINT. That is a small syntax detail that trips people up when moving SQL between databases.

Once the constraint is dropped, the two tables are no longer linked. You can insert orders with customer_id values that do not exist in the customers table. You can delete a customer without the database complaining about the orders that referenced them.

Dropping a foreign key is common during schema migrations. You might drop the constraint temporarily to bulk-load data, then re-add it once the load is complete. On a table with millions of rows, validating a foreign key on every insert is slow; dropping it, loading, and re-adding is sometimes the fastest path.

The risk: during the window when the constraint is dropped, invalid data can get in. Re-adding the constraint after the load will fail if the loaded data has orphaned rows. The cleaner approach is to validate the load before re-adding, or to use a bulk-load path that bypasses the constraint temporarily without dropping it.

CREATE INDEX builds an index on one or more columns to speed up lookups, joins, and sorts that reference those columns.

CREATE INDEX idx_customer_name
ON     customers (customer_name);

After that statement, any query that filters on customer_name can use the index instead of scanning the customers table.

For multiple columns:

CREATE INDEX idx_region_limit
ON     customers (region, credit_limit);

That index supports queries that filter on region alone, or on region and credit_limit together. It does not help queries that filter only on credit_limit, because the index is sorted by region first.

A unique index adds a constraint:

CREATE UNIQUE INDEX idx_customer_name_unique
ON     customers (customer_name);

That prevents duplicate customer names, in addition to speeding up lookups. It is equivalent to declaring a UNIQUE constraint, and in fact most databases implement unique constraints by creating a unique index behind the scenes.

Indexes speed up reads and slow down writes. Every insert, update, or delete has to maintain every index on the table. On high-write tables, the balance matters — index what queries actually need, and drop anything that is never used.

DROP INDEX removes an index and frees the storage it occupied. The table and its data are unaffected — only the index structure is removed.

-- Oracle, PostgreSQL, MySQL
DROP INDEX idx_customer_name;

-- SQL Server
DROP INDEX customers.idx_customer_name;

Most databases only need the index name. SQL Server requires the table name as well, because index names are scoped to the table rather than the schema. That is why the SQL Server syntax writes customers.idx_customer_name — the index belongs to that table.

Once the index is dropped, any query that was using it falls back to a full table scan. On small tables that is invisible. On large ones, the query that was running in milliseconds now takes seconds. That is why dropping an index is not a gratuitous cleanup operation — it is a real trade-off.

The reasons to drop an index:

• It is never used (the database's usage statistics can confirm this).
• It is made redundant by a composite index that covers the same queries.
• The write overhead on a high-insert table outweighs the read benefit.
• The column it indexed has been dropped (some databases drop the index automatically, some do not).

Before dropping in production, run the queries that used the index and confirm they still perform acceptably without it. Once it is dropped, restoring it means rebuilding the index — which on a large table can take a long time and is not instantaneous.

Constraints and triggers both enforce rules, but they work at different levels. Constraints are declarative — you state the rule and the database enforces it. Triggers are procedural — you write code and the database runs it on specific events.

Constraints — the declarative layer

CREATE TABLE customers (
    customer_id   VARCHAR2(10) PRIMARY KEY,
    customer_name VARCHAR2(100) NOT NULL,
    region        VARCHAR2(20),
    credit_limit  NUMBER(12,2),
    CONSTRAINT chk_region
        CHECK (region IN ('North', 'South', 'East', 'West')),
    CONSTRAINT chk_limit_non_negative
        CHECK (credit_limit IS NULL OR credit_limit >= 0)
);

Five constraint types cover most declarative rules: PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, and CHECK. They are fast, portable, and visible in the schema. Any tool that looks at the table sees them.

Triggers — the procedural layer

CREATE TRIGGER trg_customer_normalize
BEFORE INSERT OR UPDATE ON customers
FOR EACH ROW
BEGIN
    :NEW.customer_name := UPPER(TRIM(:NEW.customer_name));
END;

That trigger trims whitespace and uppercases the customer name before the row is written. The transformation happens automatically, for every insert and update, regardless of which application or script did the write.

Triggers can do things constraints cannot — call other code, log changes to another table, conditionally raise errors, modify the row being written. Any rule that requires logic rather than a simple check is a trigger's territory.

When to use which

Prefer constraints when they can express the rule. They are faster, clearer, and less surprising. A constraint is a rule the schema declares — anyone reading the table definition sees it. A trigger is hidden logic — a developer looking at the schema will not know it exists until they trace through the database's behaviour.

Use triggers for the cases that need them: complex validation, cross-table rules, audit trails, denormalization maintenance. When you use one, document it in the table's comments or a schema document. A trigger nobody knows about is a debugging nightmare waiting to happen.

A CHECK constraint enforces a boolean rule on a column's values. Every insert or update is checked against the rule; anything that violates it is rejected.

CREATE TABLE customers (
    customer_id   VARCHAR2(10) PRIMARY KEY,
    customer_name VARCHAR2(100),
    region        VARCHAR2(20),
    credit_limit  NUMBER(12,2),
    CONSTRAINT chk_positive_limit
        CHECK (credit_limit >= 0),
    CONSTRAINT chk_valid_region
        CHECK (region IN ('North', 'South', 'East', 'West'))
);

Two rules are enforced: credit limit must be non-negative, and region must be one of the four listed values. Any attempt to insert a customer with a negative limit or an unrecognised region fails immediately.

You can also add a CHECK to a table that already exists:

ALTER TABLE customers
ADD CONSTRAINT chk_status
    CHECK (region IS NOT NULL OR credit_limit IS NULL);

That rule says: if region is missing, credit_limit must also be missing. Adding it to an existing table validates every current row — if any row violates the rule, the ADD fails and you have to fix the data first.

The NULL subtlety

CHECK constraints allow NULL. The expression is only evaluated when the value is something other than NULL. On our data, chk_positive_limit would not reject Fiona Mart's NULL credit limit — it just does not check it.

To enforce both "must have a value" and "the value must satisfy a rule", combine them:

CHECK (credit_limit IS NOT NULL AND credit_limit >= 0)

That single CHECK rejects both NULL and negative values. On our data, adding this constraint would fail because Fiona Mart's row would violate it.

CHECK constraints are best for rules that are stable and simple. A short list of valid values, a range of allowed numbers, a required format. Rules that change frequently belong in a lookup table with a foreign key, not in a CHECK that has to be altered every time the list grows.

A composite primary key uses two or more columns together to uniquely identify each row. Neither column is unique on its own, but the combination is.

CREATE TABLE order_items (
    order_id   NUMBER(10),
    product_id VARCHAR2(10),
    quantity   NUMBER(5),
    unit_price NUMBER(10,2),
    PRIMARY KEY (order_id, product_id)
);

The primary key is on the pair (order_id, product_id). A single order can contain many products, and a single product can appear in many orders, but no order can list the same product twice.

Adding it to an existing table works the same way:

ALTER TABLE order_items
ADD CONSTRAINT pk_order_items PRIMARY KEY (order_id, product_id);

If the table currently has duplicate pairs, the ADD fails and you have to deduplicate first.

The NOT NULL implication

Every column in a composite primary key is implicitly NOT NULL. Attempting to insert a row with a NULL in any of the key columns fails, because NULL cannot be part of a unique identifier.

INSERT INTO order_items (order_id, product_id, quantity)
VALUES (NULL, 'P001', 2);   -- fails: order_id cannot be NULL

That is a feature, not a bug — a row whose identity depends on a missing value is not identifiable at all.

Composite primary keys vs surrogate keys

The alternative to a composite primary key is a surrogate key — a single auto-generated id column with no business meaning. Both work. The composite version carries meaning in the key itself (an order-item pair is meaningful); the surrogate version keeps keys small and consistent but loses that meaning.

Composite keys are the natural choice for junction tables and versioned records. Surrogate keys are common in the base entities — customers, products, orders — because they stay stable even when business definitions change. Most well-designed schemas use both: surrogate primary keys on the primary entities, composite keys on the junction tables between them.

TRUNCATE TABLE removes every row from a table while keeping the structure — columns, constraints, indexes, triggers all stay in place. The table is empty and ready to receive new data.

TRUNCATE TABLE orders;

After that statement, orders has zero rows but the same structure it had before. Any query against it returns no rows. New inserts work exactly as before.

How it differs from DELETE

DELETE FROM orders; with no WHERE clause also removes every row. The difference is in how the database does it:

• Speed. TRUNCATE is dramatically faster on large tables because it does not log each row deletion.
• Auto-increment. TRUNCATE resets any identity or sequence counter tied to the table. DELETE does not.
• Triggers. TRUNCATE does not fire row-level triggers. DELETE does.
• Rollback. DELETE can be rolled back before commit. TRUNCATE is auto-committed on Oracle and often cannot be rolled back, though PostgreSQL, MySQL, and SQL Server allow it inside a transaction.

When TRUNCATE is the right choice

Whenever you want to empty a table entirely. Reloading a staging table, resetting a test environment, clearing a cache table — all classic TRUNCATE scenarios. The speed advantage is dramatic on large tables; a table that takes minutes to DELETE every row truncates in seconds.

When it is not

When you need to keep some rows. TRUNCATE cannot be filtered — it removes everything. Any conditional cleanup uses DELETE ... WHERE.

When the table has foreign keys pointing at it. Most databases refuse to truncate a table that other tables reference, because removing all rows would violate the referenced constraint. You have to drop the constraints first or use DELETE.

On Oracle, TRUNCATE is a DDL statement and cannot be rolled back. That makes it one of those operations that either works exactly as intended or destroys data — there is no middle state. Before running it, run a SELECT COUNT(*) against the table to confirm you are about to empty the right one.

Relationships between tables are enforced by foreign keys. Two patterns cover almost every real schema.

One-to-many

CREATE TABLE customers (
    customer_id   VARCHAR2(10) PRIMARY KEY,
    customer_name VARCHAR2(100) NOT NULL
);

CREATE TABLE orders (
    order_id    NUMBER(10) PRIMARY KEY,
    customer_id VARCHAR2(10),
    order_amount NUMBER(12,2),
    CONSTRAINT fk_orders_customer
        FOREIGN KEY (customer_id) REFERENCES customers (customer_id)
);

One customer, many orders. Each order belongs to exactly one customer. The foreign key lives on the "many" side — orders — because each order row points at a specific customer.

On our data, Alice Corp has three orders and Bob Traders has two. Every order references a customer that exists in the parent table. That is the constraint at work.

Many-to-many

CREATE TABLE customer_products (
    customer_id VARCHAR2(10),
    product_id  VARCHAR2(10),
    PRIMARY KEY (customer_id, product_id),
    FOREIGN KEY (customer_id) REFERENCES customers (customer_id),
    FOREIGN KEY (product_id)  REFERENCES products  (product_id)
);

Many customers, many products. A customer can buy many products, and a product can be bought by many customers. The relationship lives in a junction table with a foreign key pointing at each side.

The composite primary key on (customer_id, product_id) prevents duplicate pairs. The same customer can appear many times (once per product), and the same product can appear many times (once per customer). Only the pair is unique.

The junction table can carry extra columns too, describing the pairing itself rather than either side alone:

CREATE TABLE customer_products (
    customer_id  VARCHAR2(10),
    product_id   VARCHAR2(10),
    first_seen   DATE,
    times_bought NUMBER(5),
    PRIMARY KEY (customer_id, product_id),
    FOREIGN KEY (customer_id) REFERENCES customers (customer_id),
    FOREIGN KEY (product_id)  REFERENCES products  (product_id)
);

first_seen and times_bought are attributes of the relationship, not of either entity. That is what junction tables are for — modelling facts that only exist at the intersection of two things.

Every major database supports both patterns with the same syntax. The design decision is about which side needs the foreign key (the "many" side in one-to-many) and whether you need a junction table at all (yes for many-to-many).