Hello There,
Thanks for your valuable time & efforts while giving support.
I recently started learning Oracle SQL and PL/SQL for my project purpose. While trying to create a foreign key over in a column in the table with modifier ON UPDATE RESTRICTED, below is the error I am getting
CREATE TABLE supplier_groups(
group_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
group_name VARCHAR2(255) NOT NULL,
constraint grp_id PRIMARY KEY (group_id));
CREATE TABLE suppliers (
supplier_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
supplier_name VARCHAR2(255) NOT NULL,
group_id NUMBER NOT NULL,
PRIMARY KEY(supplier_id),
CONSTRAINT fk_grpid
FOREIGN KEY(group_id)
REFERENCES supplier_groups(group_id) ON UPDATE RESTRICT
);
at UPDATE STATEMENT is shown with pink underlined; stating that: syntax error. partially recognized rules.
would anyone help me understand the mistake /error.
Thank you again.
The ON UPDATE clause for foreign keys is unsupported in Oracle Database. While this may work in other databases, it's invalid in Oracle SQL.
ON UPDATE RESTRICT means you can't update the parent value if there's a child row referencing it. This is the behaviour for foreign keys in Oracle Database, so you can remove this clause.