sql error - no unique constraint matching keys-CodePudding

for some reason i am getting there is no unique constraint matching given keys for referenced table "accident_location". I am using postgresql to create the table. what is the error here? as i have set the primary key of ID and address_ID on my accident_location table

CREATE TABLE IF NOT EXISTS Accident(
    ID varchar(10) NOT NULL,
    Severity INT,
    Start_Time varchar(100) NOT NULL,
    End_Time varchar(100) NOT NULL,
    Description varchar(100),
    PRIMARY KEY(ID)
);

CREATE TABLE IF NOT EXISTS Accident_Location(
    ID varchar(10),
    Address_ID INT,
    Start_lat float,
    Start_Lng float, 
    End_Lat float,
    End_Lng float,
    "Distance(mi)" float,
    PRIMARY KEY (ID,Address_ID),
    FOREIGN KEY (ID) REFERENCES Accident(ID)
);

CREATE TABLE IF NOT EXISTS Address(
    Address_ID INT,
    Number  INT,
    Street varchar(100),
    Side varchar(5) ,
    City varchar(50) NOT NULL,
    County varchar(50) ,
    State varchar(10) NOT NULL,
    Zipcode varchar(15) NOT NULL,
    Country varchar(5) ,
    Timezone varchar(30) ,
    Airport_code varchar(10),
    Location_ID INT NOT NULL,
    Weather_ID INT NOT NULL,
    PRIMARY KEY (Address_ID),
    FOREIGN KEY (Address_ID) REFERENCES Accident_Location(Address_ID)
);

CodePudding user response：

The referenced Address_ID on the Address table should be unique on table Accident_Location.

Change Accident_Location table to

CREATE TABLE IF NOT EXISTS Accident_Location(
    ID varchar(10),
    Address_ID INT constraint unx_addres_id_location unique,
    Start_lat float,
    Start_Lng float,
    End_Lat float,
    End_Lng float,
    "Distance(mi)" float,
    PRIMARY KEY (ID,Address_ID),
    FOREIGN KEY (ID) REFERENCES Accident(ID)
);

Here, I've added a unique constraint on Address_ID

Address_ID INT constraint unx_addres_id_location unique

UPDATED

Since you are referring to Address_ID of Accident_Location table as a foreign key on the Address table, this will force you to make it a unique record. That means any two rows or more should not have the same value of Address_ID. Otherwise, the foreign key Address_ID on the Address table wouldn't know which row you are referring to.

The reason you are not seeing this same error for the Accident table is that by default primary keys are unique.