Object Namespaces - Oracle to MySQL Migration

In Oracle, tables, views, procedures, functions, packages and sequences share the same namespace, meaning they cannot have identical names with other types of objects within a schema.

At the same time, constraints, indexes and triggers have own namespaces, so they can have identical names with other types of objects within a schema in Oracle.

Like Oracle, MySQL also uses shared namespaces for some database objects, while other objects have their own distinct namespaces.

Oracle:

  CREATE TABLE name1 (c1 VARCHAR2(10));
  /* Table created. */
 
  -- Index with the same name
  CREATE INDEX name1 ON name1 (c1);
  /* Index created. */
 
  -- Constraint with the same name
  ALTER TABLE name1 ADD CONSTRAINT name1 CHECK (c1 <> 'A'); 
  /* Table altered. */
 
  -- But you cannot create a view with the same name as it shares one namespace with the table
  CREATE VIEW name1 AS SELECT * FROM name1;
  /* ERROR: ORA-00955: name is already used by an existing object */

MySQL:

  CREATE TABLE name1 (c1 VARCHAR(10));
  /* Query OK, 0 rows affected */
 
  -- Index with the same name
  CREATE INDEX name1 ON name1 (c1);
  /* Query OK, 0 rows affected  */
 
  -- Constraint with the same name
  ALTER TABLE name1 ADD CONSTRAINT name1 CHECK (c1 <> 'A'); 
  /* Query OK, 0 rows affected */
 
  -- But you cannot create a view with the same name as it shares one namespace with the table
  CREATE VIEW name1 AS SELECT * FROM name1;
  /* ERROR 1050 (42S01): Table 'name1' already exists */

Let's see the resulting table definition:

MySQL:

  SHOW CREATE TABLE name1;
  /* CREATE TABLE `name1` (
        `c1` varchar(10) DEFAULT NULL,
        KEY `name1` (`c1`),
        CONSTRAINT `name1` CHECK ((`c1` <>  'A'))
      ) ... */

For more information, see Oracle to MySQL Migration.