In Oracle, if you try to explicitly define the schema for a constraint in a CREATE TABLE statement it will result in a ORA-00904: : invalid identifier error:
CREATE TABLE SCHEMA1.MY_TABLE
(
TABLE_ID NUMBER(5)
, FLAG VARCHAR2(1) DEFAULT 'F'
, CONSTRAINT SCHEMA1.MY_TABLE_PK PRIMARY KEY (TABLE_ID) -- Parser doesn't like 'SCHEMA1'
, CONSTRAINT SCHEMA1.MY_TABLE_FLAG_CK CHECK (FLAG IN ('T', 'F')) --Same issue
);
This is no big deal because the constraint OWNER defaults to the same schema as defined in the CREATE TABLE declaration (or at least in the schema you are signed into - my accounts don't have the rights to validate). This can be confirmed by pulling up the constraint in ALL_CONSTRAINTS or DBA_CONSTRAINTS and viewing the OWNER value (which would read SCHEMA1 for the two constraints above).
But if you then follow up this statement with a second duplicate statement under a different schema:
CREATE TABLE SCHEMA2.MY_TABLE
(
TABLE_ID NUMBER(5)
, FLAG VARCHAR2(1) DEFAULT 'F'
, CONSTRAINT MY_TABLE_PK PRIMARY KEY (TABLE_ID) --This constraint already exists
, CONSTRAINT MY_TABLE_FLAG_CK CHECK (FLAG IN ('T', 'F')) --This one too
);
this results in an ORA-00955: name is already used by an existing object error.
My understanding of constraints are that they are just another object on the database which I would have assumed were subject to the same ownership rules with which I'm familiar. But based on the behavior above it is clear that they do not behave like most objects on the database.
Questions
For context, I've run into a scenario at work where I had wanted to store duplicate names under different schemas (don't ask... it was inherited duplication and I'm just trying to stay consistent until I have funding to refactor). Now, I realize that I can very simply add the schema to the name and bypass this problem quickly but this rubs my OCD the wrong way so I'd like to better understand just why I can't do what I'm trying to do.
----------------------------- UPDATE ---------------------------
Okay... so I'm a fool. Please note that the above scenario cannot be repeated given a clean environment. After trying to reproduce the issue, I now realize what happened.
I'm currently working on cleaning up some DDL statements in preparation for a production release. I'm not the only developer in this environment and my offshore team has been working on these same tables. I have been editing some preexisting DDL scripts in preparation for a production release and had added some needed constraints to a few CREATE TABLE statements.
It would appear that I had failed to run my DROP scripts before running the second statement in SCHEMA2. My confusion was due to thinking that I was getting the ORA-00955 error because of the new constraints I was adding when in actuality it was because the SCHEMA2.MY_TABLE already existed in this environment. This misdiagnosis was compounded by me changing my constraint names and then rerunning all of my scripts successfully (as I must have run my DROP statements in all schemas before retesting). As a result I thought that I had observed unforeseen behavior when I really had not.
Thank you to everyone who commented for showing me the light!
What's missing in your question is the user under which you run the script. But here's a little experiment. Connected to the database as user system. I have schemas NGM42 and NGM41 available for the experiment.
CREATE TABLE NGM41.MY_TABLE
(
TABLE_ID NUMBER(5)
, FLAG VARCHAR2(1) DEFAULT 'F'
, CONSTRAINT MY_TABLE_PK PRIMARY KEY (TABLE_ID) -- Parser doesn't like 'SCHEMA1'
, CONSTRAINT MY_TABLE_FLAG_CK CHECK (FLAG IN ('T', 'F')) --Same issue
);
CREATE TABLE NGM42.MY_TABLE
(
TABLE_ID NUMBER(5)
, FLAG VARCHAR2(1) DEFAULT 'F'
, CONSTRAINT MY_TABLE_PK PRIMARY KEY (TABLE_ID)
, CONSTRAINT MY_TABLE_FLAG_CK CHECK (FLAG IN ('T', 'F'))
);
Both statements run succesfully. So what happened to the constraints?
select owner,constraint_name from all_constraints
where constraint_name in ('MY_TABLE_PK','MY_TABLE_FLAG_CK')
NGM42 MY_TABLE_PK
NGM41 MY_TABLE_PK
NGM41 MY_TABLE_FLAG_CK
NGM42 MY_TABLE_FLAG_CK
The constraints are created in the same schema as the table. As you can see they do not need to be globally unique in the database.