Clave Primaria (Primary Key)Modelado
What is a database Primary Key?
A unique, non-null column constraint serving as the immutable identifier for each individual record in a database entity table.
DEVELOPMENT & DATABASES
Database SQL Schema & DDL
Design tables, columns, relational foreign keys (FK), and generate clean DDL CREATE TABLE scripts instantly 100% in your browser.
100% local generation — Your schema design stays in your browser
Table:
Foreign Keys
No foreign keys in this table.
Click 'Add FK' to link another table.
Relational Normalization (1NF to 3NF)
Normalization organizes tables and columns to minimize data redundancy and eliminate update anomalies. 1NF enforces atomic attributes; 2NF removes partial key dependencies; and 3NF removes transitive dependencies among non-key fields.
Balance: Normalize in 3NF for transactional OLTP systems, denormalizing cautiously only for analytical OLAP workloads.
Primary Key (PK) Strategies
Auto-incrementing BIGINT keys offer maximum sequential B-Tree insertion performance; UUID v7 / ULID combine time-ordering with collision-free distributed generation; composite keys enforce business uniqueness in N:M join tables.
Guideline: Use BIGINT for high-throughput internal records, and UUID v7 for publicly exposed API resources.
Referential Integrity & ON DELETE
Foreign Keys (FK) enforce relational ACID consistency. CASCADE purges dependent child records automatically; RESTRICT / NO ACTION blocks parent deletion if dependents exist; SET NULL preserves child records while unlinking foreign references.
Safety: Apply RESTRICT on financial ledger or user entities to prevent catastrophic accidental cascade purges.
B-Tree Indexing & Foreign Keys
Adding explicit indexes on Foreign Key columns (such as user_id, order_id) eliminates full table scans on JOIN operations and prevents table-level locks during parent table updates or deletions.
Optimization: Always index columns frequently filtered in WHERE clauses, sorted by ORDER BY, or matched in JOINs.
1. Foreign Key Creation Error (MySQL Error 150)
Database engine rejects foreign key creation with a constraint creation error.
Quick Fix
Verify that both the foreign key column and target primary key share the exact same data type (e.g. BIGINT with BIGINT) and unsigned attribute.
Technical Insight
Relational storage engines require strict binary type matching and mandate that the referenced parent table/column exists prior to child creation.
2. Catastrophic Data Loss via ON DELETE CASCADE
Deleting a parent record silently obliterates thousands of associated child records across related tables.
Quick Fix
Switch the ON DELETE rule to RESTRICT or SET NULL for business-critical audit trails or transaction tables.
Technical Insight
CASCADE cascades deletions recursively across all child tables without warnings. Modern architectures favor Soft Deletes (deleted_at timestamp) or RESTRICT constraints.
3. Table Locks and Sluggish JOINs from Missing FK Indexes
JOIN queries or updates on the parent table trigger full table locks and query performance degradation.
Quick Fix
Enable the Index checkbox for every foreign key column created in the table designer.
Technical Insight
Many engines (such as PostgreSQL) do not automatically create indexes on foreign keys. Lacking a B-Tree index forces full table scans on parent deletions.
4. Circular Dependencies and DDL Execution Order
DDL script execution fails because Table A references Table B, while Table B simultaneously references Table A.
Quick Fix
Break circular references by creating tables first without the foreign key, then appending it via ALTER TABLE ADD CONSTRAINT.
Technical Insight
DDL scripts execute sequentially. Mutually referenced tables require splitting FK constraints into separate ALTER TABLE statements after table creation.
Historical Landmark#1
The 1970 Paper That Revolutionized Computing
In June 1970, British computer scientist Edgar F. Codd published 'A Relational Model of Data for Large Shared Data Banks'. This landmark paper replaced archaic hierarchical network models, establishing tables, rows, columns, and foreign keys.
Trade-off Performance#2
The Index Cost Paradox
While indexes can accelerate a SELECT query from 5 seconds to 1 millisecond (a 500,000% speedup), every additional index slows down INSERT, UPDATE, and DELETE operations because the storage engine must rebalance the on-disk B-Tree on each write.
Developer Lore#3
The Legendary MySQL 'Error 150'
For decades in MySQL/InnoDB, any subtle foreign key constraint flaw (type mismatch, missing index, duplicate identifier) returned the cryptic 'Can't create table (errno: 150)', making it one of the most visited error queries in Stack Overflow history.
Enterprise Scale#4
Schemas With Over 30,000 Tables
While modern web applications maintain between 20 and 80 tables, massive enterprise ERP systems like SAP S/4HANA and Salesforce operate over 30,000 active relational tables interconnected by hundreds of thousands of foreign keys.
Relational Modeling & Foreign Keys Glossary
Learn the fundamental concepts, protocols, and technical terminology of this tool.
Clave Foránea (Foreign Key)Integridad
What is a Foreign Key constraint?
A relational constraint linking a child table column to a parent table primary key, enforcing ACID referential consistency across entities.
Regla ON DELETE CASCADEIntegridad
What does ON DELETE CASCADE do?
A relational policy automatically deleting dependent child records when the originating parent record is dropped from the database.
Índice B-TreeRendimiento
What is a B-Tree database index?
A balanced tree data structure empowering query engines to locate records with logarithmic O(log n) time complexity, bypassing full table scans.