I am using Oracle database 23ai, free version, along with the PL/SQL developper of Allround Automations. I am getting the error ORA-02264 and though I understand its meaning (an already active constraint with same name was found where assigning the same name to two constraints is forbidden), I don't understand why this error pops up in my case.
I am trying the following code:
CREATE TABLE nations (
nations_id INTEGER GENERATED BY DEFAULT AS IDENTITY,
name NVARCHAR2(255) NOT NULL,
continent NVARCHAR2(20) NOT NULL,
habitants_at_2024 INTEGER,
capital NVARCHAR2(255) NOT NULL,
CONSTRAINT pk_nations PRIMARY KEY(nations_id),
CONSTRAINT unique_nation_name UNIQUE(name)
);
-- cities
CREATE TABLE cities(
cities_id INTEGER GENERATED BY DEFAULT AS IDENTITY,
name NVARCHAR2(255) NOT NULL,
country NVARCHAR2(255),
CONSTRAINT pk_cities PRIMARY KEY(cities_id),
CONSTRAINT unique_city_name UNIQUE(name),
CONSTRAINT fk_country FOREIGN KEY(country) REFERENCES nations(name)
);
ALTER TABLE nations ADD CONSTRAINT fk_capital FOREIGN KEY(capital) REFERENCES cities(name);
The error message:
ORA-02264: name already used by an existing constraint
pops up on the line with the ALTER TABLE statement.
However, I am quite sure there shouldn't be another constraint with the name fk_capital. To verify this, I tried the following as suggested in this question
SELECT *
FROM all_constraints
WHERE table_name = UPPER('table_name');
The command works fine, but I cannot find the constraint name in the resulting table either. What's more, I tried the following:
ALTER TABLE nations DROP CONSTRAINT fk_capital;
ALTER TABLE nations ADD CONSTRAINT fk_capital FOREIGN KEY(capital) REFERENCES cities(name);
The attempt to drop the constraint fails with the error message ORA-02443, which suggests there is no constraint with the name fk_capital. Right after that, ORA-02264 pops up again, indicating once more the name would already be in use. I made sure there is no typo - the code you're seeing is copy-pasted. Can someone tell me where I am going wrong?
Thanks in advance for all answers!
PS.: I found another post suggesting that I can find the constraint-name using
SELECT owner, constraint_type, table_name
from user_constraints
where constraint_name = 'fk_capital';
but the resulting table is empty in my case.
The names in Oracle's data dictionary appear in uppercase. Instead of querying for fk_capital, you should query for FK_CAPITAL.
SELECT owner, constraint_type, table_name
FROM user_constraints
WHERE constraint_name = 'FK_CAPITAL';