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.
new_newbie is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
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';
1
When you execute queries to check for the constraint fk_capital, it may not return results because Oracle treats it as FK_CAPITAL internally.
Steps to Resolve the Issue:
- Verify the Constraint Name: Run the following query to check for constraints in uppercase:
SELECT owner, constraint_type, table_name
FROM user_constraints
WHERE constraint_name = ‘FK_CAPITAL’;
- Check All Constraints for the Table: If the above query doesn’t work, list all constraints for the nations table to confirm its existence:
SELECT constraint_name, constraint_type
FROM user_constraints
WHERE table_name = UPPER(‘NATIONS’);
- Dropping the Constraint (If Necessary): If the fk_capital constraint exists, you can drop it using:
ALTER TABLE nations DROP CONSTRAINT fk_capital;
- Recreate the Constraint: Once confirmed or dropped, you can recreate the constraint:
ALTER TABLE nations
ADD CONSTRAINT fk_capital FOREIGN KEY (capital) REFERENCES cities(name);
Additional Tips:
Ensure No Duplicate Constraints: Confirm that no other constraint is using the name fk_capital across different schemas.
SELECT owner, table_name, constraint_name
FROM all_constraints
WHERE constraint_name = ‘FK_CAPITAL’;
Schema Ownership: If you are working in a multi-schema environment, ensure you are querying the correct schema by prefixing the table name with the schema name (e.g., schema_name.nations).
If the issue persists, it might be related to the Oracle free version limitations or a database bug.
Rahul Thakur is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
1