Error when saving a reference

[antonlyap]

Error when saving a reference

[omohokcoj]

@antonlyap can you please share your database schema in .sql format? you can use pg_dump --schema-only databasename if that's a PG database.

[antonlyap]

Here's the complete dump. Sorry if it's too long, didn't want to leave out anything important.

--
-- PostgreSQL database dump
--

-- Dumped from database version 15.3
-- Dumped by pg_dump version 15.3

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

SET default_tablespace = '';

SET default_table_access_method = heap;

--
-- Name: accounts; Type: TABLE; Schema: public; Owner: root
--

CREATE TABLE public.accounts (
    id integer NOT NULL,
    name character varying(30) NOT NULL,
    currency character(3) NOT NULL,
    bank text NOT NULL,
    opening_balance money NOT NULL
);


ALTER TABLE public.accounts OWNER TO root;

--
-- Name: accounts_id_seq; Type: SEQUENCE; Schema: public; Owner: root
--

CREATE SEQUENCE public.accounts_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.accounts_id_seq OWNER TO root;

--
-- Name: accounts_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: root
--

ALTER SEQUENCE public.accounts_id_seq OWNED BY public.accounts.id;


--
-- Name: transactions; Type: TABLE; Schema: public; Owner: root
--

CREATE TABLE public.transactions (
    id integer NOT NULL,
    date timestamp with time zone NOT NULL,
    amount_account money NOT NULL,
    amount_original money NOT NULL,
    currency character(3) NOT NULL,
    description text,
    category text DEFAULT 'Uncategorized'::text NOT NULL,
    account integer NOT NULL
);


ALTER TABLE public.transactions OWNER TO root;

--
-- Name: transactions_id_seq; Type: SEQUENCE; Schema: public; Owner: root
--

CREATE SEQUENCE public.transactions_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.transactions_id_seq OWNER TO root;

--
-- Name: transactions_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: root
--

ALTER SEQUENCE public.transactions_id_seq OWNED BY public.transactions.id;


--
-- Name: accounts id; Type: DEFAULT; Schema: public; Owner: root
--

ALTER TABLE ONLY public.accounts ALTER COLUMN id SET DEFAULT nextval('public.accounts_id_seq'::regclass);


--
-- Name: transactions id; Type: DEFAULT; Schema: public; Owner: root
--

ALTER TABLE ONLY public.transactions ALTER COLUMN id SET DEFAULT nextval('public.transactions_id_seq'::regclass);


--
-- Name: accounts accounts_pk; Type: CONSTRAINT; Schema: public; Owner: root
--

ALTER TABLE ONLY public.accounts
    ADD CONSTRAINT accounts_pk PRIMARY KEY (id);


--
-- Name: transactions transactions_pk; Type: CONSTRAINT; Schema: public; Owner: root
--

ALTER TABLE ONLY public.transactions
    ADD CONSTRAINT transactions_pk PRIMARY KEY (id);


--
-- Name: transactions transactions_fk; Type: FK CONSTRAINT; Schema: public; Owner: root
--

ALTER TABLE ONLY public.transactions
    ADD CONSTRAINT transactions_fk FOREIGN KEY (account) REFERENCES public.accounts(id);


--
-- PostgreSQL database dump complete
--

[omohokcoj]

@antonlyap the issue is caused by your account foreign key name "account" in the transaction table.
Motor Admin uses widely adopted DB table/columns naming conventions to build the CRUD UI on top if the DB schema.
https://github.com/RootSoft/Database-Naming-Convention#foreign-keys

So to make everything work you need to use account_id, transaction_id column name for foreign keys.

[antonlyap]

Renaming account to account_id fixed it. Transaction ID is only used as a primary key, so its name stays as id according to the convention. Thank you.