Check if row already exists, if so tell the referenced table the id
- by flhe
Let's assume I have a table magazine:
CREATE TABLE magazine
(
magazine_id integer NOT NULL DEFAULT nextval(('public.magazine_magazine_id_seq'::text)::regclass),
longname character varying(1000),
shortname character varying(200),
issn character varying(9),
CONSTRAINT pk_magazine PRIMARY KEY (magazine_id)
);
And another table issue:
CREATE TABLE issue
(
issue_id integer NOT NULL DEFAULT nextval(('public.issue_issue_id_seq'::text)::regclass),
number integer,
year integer,
volume integer,
fk_magazine_id integer,
CONSTRAINT pk_issue PRIMARY KEY (issue_id),
CONSTRAINT fk_magazine_id FOREIGN KEY (fk_magazine_id)
REFERENCES magazine (magazine_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
);
Current INSERTS:
INSERT INTO magazine (longname,shotname,issn)
VALUES ('a long name','ee','1111-2222');
INSERT INTO issue (fk_magazine_id,number,year,volume)
VALUES (currval('magazine_magazine_id_seq'),'8','1982','6');
Now a row should only be inserted into 'magazine', if it does not already exist. However if it exists, the table 'issue' needs to get the 'magazine_id' of the row that already exists in order to establish the reference.
How can i do this?
Thx in advance!
