<p dir="auto"><strong>Migrated issue, originally created by Anonymous</strong></p>
<p dir="auto">This problem occurs in sqlalchemy 4.7.1, using oracle 9.</p>
<p dir="auto">Case scenario:<br>
I had a job table, and a task task, and a jabs2tasks table to create a many to many relation. I created a job and a task, and added the job twice to the task (using a relation that have a secondary). len(task.jobs) returned 2. Then I saved the task and commited.<br>
I was expecting that an exception would be thrown, but it didn't. When I looked in at jobs2tasks in my SQLNavigator, I saw that only one entry was enetered.</p>
<p dir="auto">The problem is, that this behaviour is not expected. When I try to enter, for example, a task that already exists in the db (the same task_id), sqlalchemy throw a constraint exception. I think that this is exactly the same case, but with a complex primary key.</p>
<p dir="auto">Reported by Kobi Perl.</p>
<h3 dir="auto">Describe the bug</h3>
<p dir="auto">The <code class="notranslate">CreateSchema</code> DDL construct accepts a parameter <code class="notranslate">if_not_exists</code> via its base class, <code class="notranslate">_CreateDropBase</code>. However, this parameter is ignored: for example, <code class="notranslate">CreateSchema("myschema", if_not_exists=True)</code> produces <code class="notranslate">CREATE SCHEMA myschema</code> rather than <code class="notranslate">CREATE SCHEMA myschema IF NOT EXISTS</code>.</p>
<h3 dir="auto">To Reproduce</h3>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="# Assuming a Postgres server serving on localhost:5432
from sqlalchemy import create_engine
from sqlalchemy.engine import url
from sqlalchemy.sql.ddl import CreateSchema

db_url = url.URL.create(
    &quot;postgresql&quot;,
    username=&quot;postgres&quot;,
    password=&quot;postgres&quot;,
    host=&quot;localhost&quot;,
    port=5432,
    database=&quot;postgres&quot;,
)
engine = create_engine(db_url)
engine.execute(CreateSchema(&quot;myschema&quot;, if_not_exists=True))
engine.execute(CreateSchema(&quot;myschema&quot;, if_not_exists=True))"><pre class="notranslate"><span class="pl-c"># Assuming a Postgres server serving on localhost:5432</span>
<span class="pl-k">from</span> <span class="pl-s1">sqlalchemy</span> <span class="pl-k">import</span> <span class="pl-s1">create_engine</span>
<span class="pl-k">from</span> <span class="pl-s1">sqlalchemy</span>.<span class="pl-s1">engine</span> <span class="pl-k">import</span> <span class="pl-s1">url</span>
<span class="pl-k">from</span> <span class="pl-s1">sqlalchemy</span>.<span class="pl-s1">sql</span>.<span class="pl-s1">ddl</span> <span class="pl-k">import</span> <span class="pl-v">CreateSchema</span>

<span class="pl-s1">db_url</span> <span class="pl-c1">=</span> <span class="pl-s1">url</span>.<span class="pl-v">URL</span>.<span class="pl-en">create</span>(
    <span class="pl-s">"postgresql"</span>,
    <span class="pl-s1">username</span><span class="pl-c1">=</span><span class="pl-s">"postgres"</span>,
    <span class="pl-s1">password</span><span class="pl-c1">=</span><span class="pl-s">"postgres"</span>,
    <span class="pl-s1">host</span><span class="pl-c1">=</span><span class="pl-s">"localhost"</span>,
    <span class="pl-s1">port</span><span class="pl-c1">=</span><span class="pl-c1">5432</span>,
    <span class="pl-s1">database</span><span class="pl-c1">=</span><span class="pl-s">"postgres"</span>,
)
<span class="pl-s1">engine</span> <span class="pl-c1">=</span> <span class="pl-en">create_engine</span>(<span class="pl-s1">db_url</span>)
<span class="pl-s1">engine</span>.<span class="pl-en">execute</span>(<span class="pl-v">CreateSchema</span>(<span class="pl-s">"myschema"</span>, <span class="pl-s1">if_not_exists</span><span class="pl-c1">=</span><span class="pl-c1">True</span>))
<span class="pl-s1">engine</span>.<span class="pl-en">execute</span>(<span class="pl-v">CreateSchema</span>(<span class="pl-s">"myschema"</span>, <span class="pl-s1">if_not_exists</span><span class="pl-c1">=</span><span class="pl-c1">True</span>))</pre></div>
<h3 dir="auto">Error</h3>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="/.../python &quot;/.../scratch_13.py&quot;
Traceback (most recent call last):
  File &quot;/.../lib/python3.10/site-packages/sqlalchemy/engine/base.py&quot;, line 1771, in _execute_context
    self.dialect.do_execute(
  File &quot;/.../lib/python3.10/site-packages/sqlalchemy/engine/default.py&quot;, line 717, in do_execute
    cursor.execute(statement, parameters)
psycopg2.errors.DuplicateSchema: schema &quot;myschema&quot; already exists


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File &quot;/.../scratch_13.py&quot;, line 16, in &lt;module&gt;
    engine.execute(CreateSchema(&quot;myschema&quot;, if_not_exists=True))
  File &quot;&lt;string&gt;&quot;, line 2, in execute
  File &quot;/.../lib/python3.10/site-packages/sqlalchemy/util/deprecations.py&quot;, line 390, in warned
    return fn(*args, **kwargs)
  File &quot;/.../lib/python3.10/site-packages/sqlalchemy/engine/base.py&quot;, line 3108, in execute
    return connection.execute(statement, *multiparams, **params)
  File &quot;/.../lib/python3.10/site-packages/sqlalchemy/engine/base.py&quot;, line 1263, in execute
    return meth(self, multiparams, params, _EMPTY_EXECUTION_OPTS)
  File &quot;/.../lib/python3.10/site-packages/sqlalchemy/sql/ddl.py&quot;, line 77, in _execute_on_connection
    return connection._execute_ddl(
  File &quot;/.../lib/python3.10/site-packages/sqlalchemy/engine/base.py&quot;, line 1353, in _execute_ddl
    ret = self._execute_context(
  File &quot;/.../lib/python3.10/site-packages/sqlalchemy/engine/base.py&quot;, line 1814, in _execute_context
    self._handle_dbapi_exception(
  File &quot;/.../lib/python3.10/site-packages/sqlalchemy/engine/base.py&quot;, line 1995, in _handle_dbapi_exception
    util.raise_(
  File &quot;/.../lib/python3.10/site-packages/sqlalchemy/util/compat.py&quot;, line 207, in raise_
    raise exception
  File &quot;/.../lib/python3.10/site-packages/sqlalchemy/engine/base.py&quot;, line 1771, in _execute_context
    self.dialect.do_execute(
  File &quot;/.../lib/python3.10/site-packages/sqlalchemy/engine/default.py&quot;, line 717, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.DuplicateSchema) schema &quot;myschema&quot; already exists

[SQL: CREATE SCHEMA myschema]
(Background on this error at: https://sqlalche.me/e/14/f405)"><pre class="notranslate"><code class="notranslate">/.../python "/.../scratch_13.py"
Traceback (most recent call last):
  File "/.../lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1771, in _execute_context
    self.dialect.do_execute(
  File "/.../lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 717, in do_execute
    cursor.execute(statement, parameters)
psycopg2.errors.DuplicateSchema: schema "myschema" already exists


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/.../scratch_13.py", line 16, in &lt;module&gt;
    engine.execute(CreateSchema("myschema", if_not_exists=True))
  File "&lt;string&gt;", line 2, in execute
  File "/.../lib/python3.10/site-packages/sqlalchemy/util/deprecations.py", line 390, in warned
    return fn(*args, **kwargs)
  File "/.../lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 3108, in execute
    return connection.execute(statement, *multiparams, **params)
  File "/.../lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1263, in execute
    return meth(self, multiparams, params, _EMPTY_EXECUTION_OPTS)
  File "/.../lib/python3.10/site-packages/sqlalchemy/sql/ddl.py", line 77, in _execute_on_connection
    return connection._execute_ddl(
  File "/.../lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1353, in _execute_ddl
    ret = self._execute_context(
  File "/.../lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1814, in _execute_context
    self._handle_dbapi_exception(
  File "/.../lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1995, in _handle_dbapi_exception
    util.raise_(
  File "/.../lib/python3.10/site-packages/sqlalchemy/util/compat.py", line 207, in raise_
    raise exception
  File "/.../lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1771, in _execute_context
    self.dialect.do_execute(
  File "/.../lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 717, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.DuplicateSchema) schema "myschema" already exists

[SQL: CREATE SCHEMA myschema]
(Background on this error at: https://sqlalche.me/e/14/f405)
</code></pre></div>
<h3 dir="auto">Versions</h3>
<ul dir="auto">
<li>OS: OSX 12.0.1</li>
<li>Python: 3.10.2</li>
<li>SQLAlchemy: 1.4.22</li>
<li>Database: PostgreSQL 13.4</li>
<li>DBAPI (eg: psycopg, cx_oracle, mysqlclient): psycopg</li>
</ul>
<h3 dir="auto">Additional context</h3>
<p dir="auto">From inspection of the source, it seems that <code class="notranslate">CreateTable</code> and <code class="notranslate">CreateIndex</code> are the only subclasses of <code class="notranslate">_CreateDropBase</code> that make use of the parameter; from this it is not clear why the parameter exists on the base class instead of the specific subclasses that actually use it.</p>
<p dir="auto">I appreciate that implementing support for this might be confounded by different support for this feature in different database engines. However I see that it is supported for indices even though MySQL doesn't support that syntax as far as I can see (and the <code class="notranslate">IF NOT EXISTS</code> is even added in the <code class="notranslate">MySQLDDLCompiler</code>, strangely). If support is not feasible, it might be worthwhile moving the <code class="notranslate">if_not_exists</code> parameters onto just the classes that support it.</p>
<p dir="auto">All of these observations also apply to the <code class="notranslate">if_exists</code> parameter too.</p>