创建含复合PRIMARY KEY与FOREIGN KEY的REP_CONTRACTS表遇问题求助

Hey there! Sorry you're hitting a wall trying to create your REP_CONTRACTS table—let's walk through the most common pitfalls and get you sorted out. First, a few key details would help narrow this down fast:

• Exact error message text: Databases give super specific hints about what's broken, so sharing this is critical.
• Structure of your parent tables: What do your REP and STORE tables look like? Specifically, their primary/unique key definitions.
• The actual CREATE TABLE code you tried: Even small syntax quirks can cause failures across different database systems.

Standard Working Example #

Here's a clean, compliant version of the table creation script that matches your requirements (adjust column types to match your actual data needs):

CREATE TABLE REP_CONTRACTS (
    REP_ID INT NOT NULL,
    STORE_ID INT NOT NULL,
    QUARTER VARCHAR(10) NOT NULL, -- Use INT (1-4) if that fits your data better
    -- Add your other columns from the chart here (e.g., SALES_GOAL DECIMAL(12,2), etc.)
    
    -- Composite primary key
    PRIMARY KEY (REP_ID, STORE_ID, QUARTER),
    -- Foreign key to REP table
    FOREIGN KEY (REP_ID) REFERENCES REP(REP_ID),
    -- Foreign key to STORE table
    FOREIGN KEY (STORE_ID) REFERENCES STORE(STORE_ID)
);

Common Causes of Failure #

If your script is similar but still throwing errors, check these:

• Missing parent tables: Double-check that REP and STORE exist in your database, and their names match exactly (case sensitivity matters in some systems like PostgreSQL on Linux).
• Mismatched data types: The REP_ID in REP_CONTRACTS must be the exact same type as the REP_ID in the REP table (e.g., INT vs BIGINT or VARCHAR(5) vs VARCHAR(10) will break the foreign key).
• Invalid foreign key targets: The column you're referencing in REP/STORE must be either a primary key or have a unique constraint—you can't create a foreign key to a regular, non-unique column.
• Syntax quirks per database: Some systems (like Oracle) require explicit constraint names for foreign keys, e.g., CONSTRAINT fk_rep_contracts_rep FOREIGN KEY (REP_ID) REFERENCES REP(REP_ID).
• Hidden null issues: While composite primary key columns are implicitly NOT NULL, if your parent table's primary key allows nulls (which it shouldn't!), that could cause conflicts.

Once you share the error message and your actual code, we can pinpoint the exact issue.

内容的提问来源于stack exchange，提问作者Atheya