Issue 1:
Migrated issue, originally created by Anonymous

This problem occurs in sqlalchemy 4.7.1, using oracle 9.

Case scenario:

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.

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.

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.

Reported by Kobi Perl.

Issue 2:
Describe the bug

The CreateSchema DDL construct accepts a parameter if_not_exists via its base class, _CreateDropBase. However, this parameter is ignored: for example, CreateSchema("myschema", if_not_exists=True) produces CREATE SCHEMA myschema rather than CREATE SCHEMA myschema IF NOT EXISTS.

To Reproduce

# 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(
"postgresql",
username="postgres",
password="postgres",
host="localhost",
port=5432,
database="postgres",
)
engine = create_engine(db_url)
engine.execute(CreateSchema("myschema", if_not_exists=True))
engine.execute(CreateSchema("myschema", if_not_exists=True))

Error

/.../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 <module>
engine.execute(CreateSchema("myschema", if_not_exists=True))
File "<string>", 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)

Versions

OS: OSX 12.0.1

Python: 3.10.2

SQLAlchemy: 1.4.22

Database: PostgreSQL 13.4

DBAPI (eg: psycopg, cx_oracle, mysqlclient): psycopg

Additional context

From inspection of the source, it seems that CreateTable and CreateIndex are the only subclasses of _CreateDropBase 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.

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 IF NOT EXISTS is even added in the MySQLDDLCompiler, strangely). If support is not feasible, it might be worthwhile moving the if_not_exists parameters onto just the classes that support it.

All of these observations also apply to the if_exists parameter too.