Hi,
We solved this a long time ago using an extra function, which gets called after
SQLAlchemy did its part. The function queries all constraints, and renames the
ones which look like NOT NULL constraints having a name starting with SYS_.
This method works well in practice, and since we have full control over schema,
we don't have to worry about corner cases (e.g. differentiating between
table-level CHECK vs. "real" NOT NULL constraints).
I have started working on integrating this into SQLAlchemy, and I have a patch
which allows you to write
Column('foo', Integer, nullable=NotNull(name='foo_nn'))
And that ends up generating
CREATE TABLE ... (foo INTEGER CONSTRAINT foo_nn NOT NULL ...)
It also supports using the naming convention machinery to automatically
generate names for all NOT NULL constraints which do not have an explicit name.
The change is a bit complex compared to targeting just a single dialect, but I
really wanted the integration with MetaData.naming_convention, and that
required some extra hoops. I plan to contribute the patch, but that may take
some time.
Gabor
> -----Original Message-----
> From: [email protected]
> [mailto:[email protected]] On Behalf Of Thijs Engels
> Sent: 04 December 2015 13:25
> To: [email protected]
> Subject: [sqlalchemy] Explicitly name the not null constraint for an Oracle
> primary key
>
> Dear all,
>
> For maintenance purposes I have aiming to ensure that ALL database
> constraint have an explicit name (as recommended here as well:
> http://alembic.readthedocs.org/en/latest/naming.html)
>
> With the NOT NULL constraints this can be done by adding an explicit
> CheckConstraint. However at least for Oracle the database ends up with one
> constraint which is added by the Primary Key. Is there a way to either avoid
> this automated creation (and add the NOT NULL constraint
> manually) or if not; is it possible to explicitly provide a name for this
> constraint?
>
> class Example(Base):
> __tablename__ = "example"
> __table_args__ = (
> PrimaryKeyConstraint("entry_id", name="example_pk"),
> CheckConstraint('"ENTRY_ID" IS NOT NULL',
> name="example_entry_nn"))
>
> id = Column("entry_id", Integer)
> name = Column(String(64))
>
> (not claiming the explicit not null constraint is the prettiest, but it does
> give
> the result I am after)
>
> When looking at this database SQL Developer indicates these constraints:
>
> EXAMPLE_PK Primary_Key
> EXAMPLE_ENTRY_NN Check "ENTRY_ID" IS NOT NULL
> SYS_C00109085 Check "ENTRY_ID" IS NOT NULL
>
> Question is how to get rid of the constraint with the generated name
> (SYS_C00109085)...
>
> Thanks in advance,
>
> Thijs
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to [email protected].
> To post to this group, send email to [email protected].
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
--------------------------------------------------------------------------------
NOTICE: Morgan Stanley is not acting as a municipal advisor and the opinions or
views contained herein are not intended to be, and do not constitute, advice
within the meaning of Section 975 of the Dodd-Frank Wall Street Reform and
Consumer Protection Act. If you have received this communication in error,
please destroy all electronic and paper copies; do not disclose, use or act
upon the information; and notify the sender immediately. Mistransmission is not
intended to waive confidentiality or privilege. Morgan Stanley reserves the
right, to the extent permitted under applicable law, to monitor electronic
communications. This message is subject to terms available at the following
link: http://www.morganstanley.com/disclaimers. If you cannot access these
links, please notify us by reply message and we will send the contents to you.
By messaging with Morgan Stanley you consent to the foregoing.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
