Error during foreign key creation: Invalid references-CodePudding

I have 2 tables and I want to create a foreign key constraint in the second table. This is what I tried:

Table 1:

CREATE TABLE OFFICERIDECOMPLIANCE.REMINDER_RULE_M 
(
    REMINDER_RULE_M_D int IDENTITY(1,1) NOT NULL,
    COMMUNICATION_MODE nvarchar(255) NOT NULL,
    REMINDER_TO nvarchar(255) NOT NULL,
    REMINDER_VALUE varchar(255) NOT NULL,
    REMINDER_CONDITION varchar(255) NOT NULL,
    REMINDER_TO_CUSTOM varchar(255)
)

Table 2:

CREATE TABLE REMINDER_AUDIT 
(
    REMINDER_AUDIT_D int IDENTITY(1,1) NOT NULL,
    ACTION varchar(255) NOT NULL,

    CONSTRAINT FK_b892318b20e5bbe162722ea5946 
        FOREIGN KEY (REMINDER_RULE_M_D) 
                REFERENCES REMINDER_RULE_M(REMINDER_RULE_M_D),
    OLD_VALUE  nvarchar(1024) NOT NULL,
    NEW_VALUE nvarchar(1024) NOT NULL,
)

I get an error running the second SQL query:

Reason: SQL Error [1769] [S0001]: Foreign key 'FK_b892318b20e5bbe162722ea5946' references invalid column 'REMINDER_RULE_M_D' in referencing table 'REMINDER_AUDIT'.

CodePudding user response：

You do not need to write Foreign Key

CREATE TABLE REMINDER_AUDIT (
    REMINDER_AUDIT_D int IDENTITY(1,1) NOT NULL,
    ACTION varchar(255) NOT NULL,
    CONSTRAINT FK_b892318b20e5bbe162722ea5946 REFERENCES REMINDER_RULE_M(REMINDER_RULE_M_D),
    OLD_VALUE  nvarchar(1024) NOT NULL,
    NEW_VALUE nvarchar(1024) NOT NULL,
)

CodePudding user response：

As the error clearly tells you - you don't have a column in your second table.

You must have a column in order to create a FK constraint - the FK constraint does NOT create a column in your table - it just establishes a constraint between existing tables and columns.

So try this for your second table:

CREATE TABLE REMINDER_AUDIT 
(
    REMINDER_AUDIT_D int IDENTITY(1,1) NOT NULL,
    ACTION varchar(255) NOT NULL,

    -- define the column!
    REMINDER_RULE_M_D int NOT NULL,

    -- I'd strongly recommend trying to come up with a more
    -- intuitive and useful naming convention for your FK constraints!
    CONSTRAINT FK_b892318b20e5bbe162722ea5946 
        FOREIGN KEY (REMINDER_RULE_M_D) 
                REFERENCES REMINDER_RULE_M(REMINDER_RULE_M_D),
    OLD_VALUE nvarchar(1024) NOT NULL,
    NEW_VALUE nvarchar(1024) NOT NULL,
)

I just guessed that REMINDER_RULE_M_D is NOT NULL - you might need to adapt this (if it's an optional key).