django

Django (@djangoproject): 8m 35s on one Blacksmith job, 65s on boxd. Tests split on both: 2m 08s vs 65s. 19,899 tests. One slow test module sets the time at any shard count. django/django t.co

The New HTTP QUERY Method, and Using It in Django This is my first post on here, so bear with me. It's about the new HTTP QUERY method, and a small package I've put together so anyone can use it with Django REST framework now, rather than waiting for more widespread adoption. But mostly it's about the problem that made me care about it in the first place. The problem: GET URLs that kept growing I look after a Django project for a client where most of the app is big filterable, sortable tables. They're server-side DataTables, backed by django-rest-framework-datatables, with django-filter doing a lot of the filtering on the backend. It's a setup I really like - it's simple to set up and modify over time, and all the searching, sorting and paging happens in the database. The issue is how DataTables asks. In server-side mode, by default, it sends everything as a GET query string. Not just the search box and the page, but six parameters for every single column (columns[3][data], columns[3][searchable], columns[3][search][value] and so on), plus the ordering, plus whatever extra filters you bolt on. Our main table has 21 columns, and on top of that the filter panel adds around 40 more parameters - booleans, ranges, multi-selects, tags, and so on. When I rebuilt a request roughly from today's table, a relatively simple URL came out at about 4.8KB. Pick a handful of options in one of the multi-selects and it's well past 6KB. Gunicorn's default limit on the request line is 4094 bytes. So, unsurprisingly, years ago, it fell over, and left me scrambling to try and work out why, and how to fix it. My fix back in November 2022 was a commit called, in full, "Attempt to fix gunicorn error.", which added --limit-request-line 0 to the gunicorn start script. Zero means no limit at all. That worked, but it never sat right with me. I have always felt there must be a better approach. Why not just use POST? The obvious answer is to switch the tables to POST and put everything in the body. But it's a read pretending to be a write. POST says "this changes something", so it isn't cacheable, it isn't safe to retry, it needs a CSRF token for what is just a search, and any permission class that checks for safe methods treats it as a write. With DRF in particular, IsAuthenticatedOrReadOnly and friends would see a list request as an attempt to create something. You end up working around the framework instead of with it. So it was either a URL nobody should have to look at, or a request that lies about what it does. Selfishly, I just wanted my tables to work properly, but I think a lot of people are in the same spot. What QUERY is RFC 10008 defines a new HTTP method, QUERY. It's the first new method since PATCH. The short version is that it's safe and idempotent like GET, but it carries a request body like POST. It fills the gap between GET and POST exactly, which makes it a perfect fit for dynamic filtering - search screens, filter panels and big tables like mine, where users can combine any number of filters. Filters go in the body, so the size limit is one you control rather than the tightest thing on the path, and the request is still honestly a read, so retries, permissions and (in principle) caching all behave the way they should. Where things are at Django has merged QUERY support for 6.2 (django/django#21855, by Michiel Beijen). "query" is now in View.http_method_names, so a class-based view just defines a query() method. It's treated as safe throughout, so it's exempt from CSRF, cache_page and the cache middleware don't cache it, and the test client gets RequestFactory.query(). That's the foundation, but I don't write plain Django views for my APIs. I use packages like DRF, and DRF (along with the packages around it) doesn't know about QUERY yet. When I talked through how I could help make this new method easily adoptable with my mentor, Tim Schilling (fantastic, by the way, if anyone is looking for coding-based mentoring, whether that's technical support or career support), his suggestion was to give people something they can use now, as well as contributing to the wider community directly. So here we are. drf-query-method Until the rest of the ecosystem catches up, I've put together drf-query-method, a small package that adds QUERY support to DRF today. pip install drf-query-method Call patch() once at startup, for example in an AppConfig.ready(), and give your viewset a query() handler: import drf_query_method drf_query_method.patch() class NoteViewSet(ModelViewSet): queryset = Note.objects.all() serializer_class = NoteSerializer filter_backends = [SearchFilter, OrderingFilter] search_fields = ["title"] ordering_fields = ["created", "title"] def query(self, request, *args, **kwargs): return self.list(request, *args, **kwargs) Now QUERY /notes/ goes to query(), and the filters read their parameters from the body: QUERY /notes/ HTTP/1.1 Content-Type: application/json {"search": "django", "ordering": "-created,title"} If you'd rather not patch DRF, there are opt-in classes with the same names as DRF's (drf_query_method.routers, .viewsets, .filters, .permissions, .test), so it's an import change rather than a code change. It also works on older Django. Django 6.2 adds QUERY itself; on 4.2 to 6.1 the package adds it for you (add_query_method(), which patch() calls). A quick word on CSRF, because it's one of the nice things about QUERY: you don't need a token. It's a safe method, so Django 6.2 skips the CSRF check for it, just as it does for GET. Older versions of Django's CSRF middleware only know about GET, HEAD, OPTIONS and TRACE, so on 4.2 to 6.1 the package teaches it that QUERY is safe too. Either way, you don't send a CSRF token, even with session authentication. It's still protected against cross-site requests by CORS: browsers can only send QUERY from JavaScript, and a cross-origin QUERY needs your server's permission first. The rule is the same as for GET - a query() handler should never change anything. Closing the loop on DataTables This was the bit I actually wanted. If django-filter is installed, patch() makes DjangoFilterBackend read the body too, and as of 0.2.0 it does the same for django-rest-framework-datatables - its filter backends, pagination and renderer. So the table setup becomes: new DataTable("#notes", { serverSide: true, ajax: { url: "/api/notes/?format=datatables", type: "QUERY", }, columns: [{data: "title"}, {data: "created"}], }); jQuery sends data as a form-encoded body for any method other than GET and HEAD, which is the shape django-rest-framework-datatables already expects. format=datatables stays in the URL, and everything else moves into the body. And there's no CSRF token to send, because QUERY is a safe method. htmx supports it too For htmx lovers like myself, who are probably wondering whether this works with htmx - it does, and htmx got there first. htmx 4 (released on 28 August 2026) adds an hx-query attribute. It sends a QUERY request with the form values in the body, the same way hx-post does, rather than squeezing them into the URL: <form hx-query="/notes/search/" hx-target="#results"> <input name="search"> <select name="ordering">...</select> </form> htmx form-encodes the values, and filter_params merges a form-encoded body over the URL parameters, so SearchFilter, OrderingFilter and (with patch()) django-filter all pick them up with nothing extra. And as with DataTables, there's no CSRF header to add. The one thing to know is that htmx 4 sits under the next tag on npm, with 2.x still on latest, so you have to ask for version 4 explicitly. htmx 2 doesn't have hx-query. It's on PyPI (0.3.0 at the time of writing), and the docs are here. The plan: make this package redundant I'd honestly rather this package didn't need to exist for long. The aim is for the main packages to support QUERY themselves, at which point you can drop drf-query-method from your dependencies and rely on them instead. To help with that, it deliberately uses the same names as the upstream work - query() handlers and request.filter_params - so removing it should mean deleting a line or two rather than rewriting your views. DRF. Back in August I opened a discussion on DRF about supporting QUERY. DRF describes itself as essentially feature-complete, and the reply at the time was, reasonably, to land it in Django first. As that's now happened, I've opened a PR to update DRF accordingly encode/django-rest-framework#10091. It adds QUERY to SAFE_METHODS and the permission maps, routes it to a viewset's query() on the list route, adds request.filter_params for the filters to read, and adds .query() to the test client. django-filter. If and when the DRF change is merged, I'd like to contribute the matching change to django-filter, so DjangoFilterBackend reads QUERY bodies through the same filter_params idea. I've already worked out what needs to change. drf-spectacular. Schema generation currently crashes with KeyError: 'query' on any view with a query() handler, so I've opened a one-line fix: tfranzel/drf-spectacular#1553. Once those land, the main ecosystem supports QUERY and drf-query-method has done its job. If it doesn't work out - some big packages are understandably conservative about adopting new things too quickly - the package stays as the fix, and I'll keep it maintained. Things to watch out for It's a new method, so not everything knows about it yet. - CORS. QUERY isn't a CORS-safelisted method, so cross-origin requests get a preflight and the server has to allow it. With django-cors-headers, add it to CORS_ALLOW_METHODS. - Proxies, CDNs and firewalls. Some reject methods they don't recognise. Check it actually reaches Django through everything in front of it. - Caching. Responses are cacheable in principle, but caches generally don't key on request bodies, and Django's cache_page won't cache QUERY. Don't expect it to be cached. - The browsable API can't send QUERY, so you'll be testing with curl, fetch() or the test client. Feedback welcome This is my first real go at contributing to DRF, so I'd really appreciate people kicking the tyres. If you've got a filter-heavy API or a big DataTable, try it and tell me what breaks: - Package: pypi.org (GitHub, docs) - DRF pull request: encode/django-rest-framework#10091 - drf-spectacular fix: tfranzel/drf-spectacular#1553 And if you think QUERY belongs in DRF itself (or don't), a comment on the pull request would help the maintainers see whether people actually want it. Thanks for reading!

deps/accepted/0020-annual-release-cycle.rst at main · django/deps github.com/django/deps/blob/main/accepted/0020-an… Django のリリースサイクルが変わってた。あとバージョン番号が 2028.0 とかになるらしい。凄くワカル。うちもこれにならおうかな。

PHP のパッチは普段いつ出るのか。php2shell で初めての事前予告と、25分早い公開と、2年止まった修正 2026年10月21日の夜、日本時間23時の「PHP 修正版の公開」を待って待機していた人は多かったと思います。 私もそのひとりでした。そして待ちながら、ふと引っかかったことがあります。PHP のセキュリティ修正って、そもそも普段は予告なんてしていましたっけ。 前回の記事『修正版は23時のはずが22時31分に出ていた。php2shell で日本の約200組織が止まった夜の時系列』では、あの夜に何が起きたかを時刻つきで記録しました。この記事はその続きで、PHP の修正が普段いつどう出るのか、php2shell で何が異例だったのか、次回どこを購読しておけばよいかが分かります。 想定読者は、PHP を使っている、または基盤に PHP が含まれるサービスを運用しているエンジニアと運用担当者です。脆弱性の技術的な中身や対策手順は扱いません。 23時のはずが、22時31分から出ていた 当夜に国内で何が起きたかの時系列は、前回の記事を参照してください。ここでは PHP 側の「出し方」だけを追います。 予告は「14:00 UTC」、日本時間で23時ちょうどでした。「around」も「no later than」も付いていない、幅のない書き方です。 ところが実際には、修正版は23時より前から取得できました。一次記録（リポジトリの push 記録、ページの Last-Modified、GitHub API の公開時刻）で確認できた順番は次のとおりです（差は秒単位の記録から算出しています）。 時刻（JST） 公開されたもの 予告の23:00との差 22:31 修正版の配布ファイル（tarball）を公開リポジトリへ push 約28分前 22:33〜22:34 php.net の告知5本（1回の push でまとめて）、リリースページ、配布ページ 約26〜27分前 22:34 GitHub のセキュリティ勧告（GHSA）2件 約26分前 22:35 サポート終了版向けパッチのブランチ 約24分前 22:48〜22:54 Windows 版、CVE レコード 約5〜11分前 23:00 php.announce のメール告知（23:00:28 と 23:00:57 の2通） 予告どおり 23:06〜23:09 GitHub Release 4件 7〜9分後 配布物と勧告が22時31分から35分にかけて続けて上がり、メールだけがきっちり23時。これが事実です。 普段の PHP は公開時刻を決めておらず、たとえば9月24日のリリースでは4本の告知が UTC の10時04分から15時28分まで、約5時間半に散らばっていました。今回は逆に4バージョンの告知を1回の push にまとめ、勧告と配布物を数分間に揃えています。ただし「勧告と php.net の告知が先に出て、メールと GitHub Release が後から追う」順序自体は9月24日と同じで、その普段どおりの順序を23時の手前に置いた結果、予告より前に見えた、と整理できます。 言えることと、言えないこと ここからは推測です。私は「23時に配布物と告知と勧告が揃うように、約25分前から順に載せていった段取り」が最も自然だと考えています。根拠は、告知5本が前夜に執筆済みで1回の push でまとめて出ていること、php.net はミラーや CDN への反映に時間がかかること、2人のリリースマネージャーがメール告知を23時の1分以内に送っていることの3つです。 ただし、PHP 側が「早めた」「準備ができ次第出した」と述べた記録は見つかりませんでした。予告文には「詳細が公になるか悪用の証拠が出たら早める権利を留保する」とありますが、この約25分がその条項の発動だと PHP が述べた記録もありません。 ! 「21時59分に公開されていた」という情報が国内で広まりましたが、これは誤りです。21時59分は php.net のリポジトリに刻まれた作業時刻（rebase の時刻）で、公開ではありません。実際の push は22時33分でした。したがって「約1時間の前倒し」も誤りで、実際は約25分です。 PHP のセキュリティリリースは普段いつ出るのか: 4週ごとの木曜に、黙って出す この「予告つき、時刻合わせ」の出し方は、PHP の平時とはかけ離れています。平時のセキュリティ修正は、報告から公表まで次の6段階を通ります。 1. 報告は非公開で届く。 窓口は GitHub の Private Vulnerability Reporting か security@php.net で、報奨金はありません。 2. 深刻度は High / Medium / Low の3段階。 High と Medium は非公開のセキュリティ用リポジトリで修正します。 3. CVE は PHP Group が自分で振る。 PHP Group は2019年から CNA（CVE 採番機関）で、外部の審査待ちに依存しません。 4. 修正は4週ごとの定例リリースに同梱する。 曜日は木曜が慣例で、臨時リリースは「高リスクの問題」の例外扱いです。報告者への目安も「通常は次の月次リリース」と書かれています。 5. 公表は「Released!」記事に一言。 php.net の「PHP 8.x.y Released!」記事に「This is a security release」と添えられるだけで、技術詳細は GitHub Security Advisory（GHSA）と CVE に載ります。 6. 日時の事前予告はしない。 php.net のニュース記事は2015年1月から2026年10月までで889件ありますが、公開日時を事前に予告した記事は、今回の2026年10月20日（UTC。日本時間では21日未明）の1件だけでした（2015年より前は未確認です）。 つまり平時の PHP は、利用者から見ると「木曜の朝に起きたら新しいバージョンが出ていて、よく読むとセキュリティ修正が入っていた」という静かな出方をします。 今回の php2shell で異例だったこと 平時の流れを頭に置いたうえで、今回の php2shell（CVE-2026-109618 / CVE-2026-109619）の公表を並べます。「手順の変化」と「人と組織の変化」の2つに分けました。 # 項目 普段の PHP 今回 手順の変化 1 日時の事前予告 しない 10月20日に日時を予告 2 リリース日 4週ごとの定例木曜 定例日10月22日を1日前倒し 3 告知の形 「Released!」記事に一言 別立ての勧告記事で緩和策まで 4 サポート終了版 更新しない 7.4〜8.1 にソースパッチ 人と組織の変化 5 事前共有の相手 主要ディストリ 重要インフラ事業者にも緩和策 6 協調の範囲 セキュリティチームと報告者 OpenAI が政府・インフラと調整 7 修正までの期間と意思決定 次の月次リリースが目安 2年近く停滞、多数決へ転換 順に補足します。 予告（1）は、php.net ニュースで少なくとも2015年以降で初めてでした。 予告の原文はこうです。 The PHP security team would like to give advance notice of an upcoming security release. It is scheduled for Wednesday, 21 October 2026 at 14:00 UTC. It addresses two critical vulnerabilities, both rated CVSS v4.0 9.2. All PHP users are encouraged to prepare now so they can upgrade as soon as the release is available. We reserve the right to release earlier should details of either vulnerability become public, or should evidence of active exploitation appear in the wild. 出しているのは日時、件数、深刻度だけです。予告が出た時点ではすでに CVE 番号2件が SNS で出回っていたので、予告に含まれる情報は流出していた分より少ないことになります。少なくとも「予告が先に攻撃者へ情報を与えた」という順序ではありません。 Foundation は、予告はもともと出す計画で、流出を受けて前倒しして出したと書いています。ただし「なぜ事前予告という手段を選んだのか」そのものは書かれていません。説明があるのは1日前倒しの理由で、「ディストリがまだパッケージング中で、即時リリースでは多くの利用者に簡単な更新手段がない」という趣旨でした。 リリース日（2）は、もともと定例日でした。 直近の定例リリースが9月24日（木）で、その4週後が10月22日（木）です。PHP は「普段どおりの定例日に出す予定を、予告付きの特別扱いにしたうえで、情報流出を受けて1日前倒しした」というのが正確な姿です。 告知の形（3）とサポート終了版（4） は、別立ての勧告記事で影響する構成と暫定緩和策まで詳述し、7.4 / 8.0 / 8.1 向けには最小限のソースパッチを専用ブランチで提供しました（正式リリースではありません）。 事前共有（5）と協調（6） では、一部の重要インフラ事業者に緩和策を先行提供し、OpenAI が Cloudflare などのインフラ事業者や米政府機関との調整まで担いました。報告者側の動画の誤公開と、配布先の先行公開は次の節で扱います。 「日時だけ先に出す」は PHP にとっては初めてでも、業界全体では珍しい手法ではありません。主なプロジェクトの公開ポリシーを並べておきます。 プロジェクト 日時の事前予告 詳細の事前共有 PHP（平時） しない 主要ディストリに共有する規定のみ OpenSSL する。通常1週間前に日時と深刻度だけ 重大なものは OS ベンダー等へパッチ付きで通常2週間前 Django する。約1週間前に日時と深刻度 同時期に OS ベンダー等へ詳細とパッチを署名付きで送る curl 一般向けの規定なし Linux ディストリの非公開リストに7日前以内に草案とパッチ Rails しない 誰にも事前通知しない（漏洩リスクを避けるため、と明記） Linux カーネル しない 修正ができ次第出す。延期は最大7日、例外で14日 PHP は今回、平時の Rails 寄りの「黙って出す」運用から、OpenSSL 寄りの「日時だけ先に出す」運用へ、一回限りで切り替えた形になります。 php2shell はなぜ漏れたのか。4つの穴と、特定できない最初の流出元 協調開示は「予定日の2日前まではうまくいっていた」と PHP Foundation は書いています。崩れ方は1つではなく、性質の違う4つの穴が1日のうちに重なりました。 穴1: 出所不明の投稿。 10月20日15時（UTC）ごろ、CVE 番号2件と「CVSS 10.0」「公表は10月22日」という情報がフォーラムと SNS に出回り始めました。確認できる最古の公開投稿は Hacker News への書き込みで、投稿者は「信頼できる情報源から聞いた」と書き、翌朝には「協調開示の対象かと尋ねたら違うと言われた」と釈明しています。情報源の身元は書かれておらず、PHP Foundation も最初の流出元を名指ししていません。 穴2: 報告者の動画。 同日20時（UTC）ごろ、報告者の一社である Calif が攻撃の実演動画を誤って公開した、と PHP Foundation は書いています。元の投稿は削除されましたが、複製が SNS に広まりました。Calif 自身がこの件を説明した記述は、今回の調査では見つかっていません。 穴3: 配布先の判断。 10月21日の朝（UTC。時刻は Foundation の記述と Hacker News 上の指摘で食い違います）、修正を事前に受け取っていた Chainguard が、修正入りのコンテナイメージを公開予定より前に公開しました。Foundation はこれを「一方的に禁輸（公開まで伏せる約束）を破った」と非難し、Chainguard の CEO である Dan Lorenc は Hacker News で「野良で攻撃が出たので早めに出す判断を私がした」と述べています。 穴4: 公的な追認。 10月20日18時26分（UTC）、セキュリティ企業 watchTowr が X で、CVE 番号つきで「本物だと確認できる」と公に追認しました。流出した情報に信頼性を与えた形です。 PHP セキュリティチームの Alexandre Daubois は個人ブログで、投稿、動画、追認を並べて「待たないことを選んだ人が何人もいて、PHP はそのせいで公開を1日早めた」と述べています。犯人探しをしても特定できる相手はおらず、言えるのは「最初の流出元は不明で、そのあとに報告者の誤公開、配布先の判断、第三者の追認が重なった」までです。OpenAI は「通知範囲の広さと漏洩リスクをどう比べるべきか」と問いを公開し、意見を募っています。 PHP Foundation が「構造的な失敗」と認めた、修正が2年近く止まっていた理由 技術的なトレードオフだけでなく、意思決定の仕組みと人手の不足が修正を止めていた、というのが Foundation 自身の説明です。 7つの異例のうち、Foundation が最も重く受け止めているのはこの「2年の停滞」です。 経緯は Foundation のブログに書かれています。2024年、外部のセキュリティ企業 Quarkslab による監査（OSTIF 経由、ドイツの Sovereign Tech Agency の資金）が行われました。その指摘を直す過程で、Foundation 契約のコア開発者が、メモリ不足時の処理に問題の兆候があることを見つけました。 ところが修正は進みませんでした。修正案は複数あったものの、どれも「後方互換の破壊」「新たなサービス拒否の余地」「多数のサードパーティ拡張の改修が必要」のいずれかを伴いました。全会一致で動くチームはどの案も選べず、任意コード実行に至る具体的な経路も見つかっておらず、エンジンの深部を知る少人数は多忙でした。 動いたのは2026年です。7月30日にセキュリティ企業 Calif が OpenAI の「Patch the Planet」枠で PHP の監査を始め、8月11日に無条件のリモートコード実行を報告しました。同じ日に OpenAI も独立して同じ根本原因を報告し、ここから修正が本格化します。 Foundation はこの遅れを「構造的な失敗だった」と認めました。今後は、トレードオフで行き詰まった修正を早く経営層へ上げる仕組みを作り、意思決定を全会一致から多数決に変えると表明しています。The Next Web は「PHP は2024年に知っていたが、修正で合意できなかった」と見出しを付けました。 PHP の脆弱性情報はどこで購読するか 今回「23時」の情報を SNS の伝言で知った人は、次に向けて一次情報の購読先を1つ持っておくと楽になります。 php.announce メーリングリスト: リリース告知と、今回のような予告が配信されます。購読は php.net のメーリングリスト一覧から。 GitHub の php/php-src の Security advisories ページ: 技術詳細と暫定緩和策はここに載ります。 使っているディストリのセキュリティ情報: Debian の debian-security-announce、Ubuntu の USN、Red Hat の RHSA など。多くの人に実際に更新が届くのはここです。 JVN iPedia: 日本語で CVE 番号から検索できます（今回の CVE の掲載は、執筆時点で未確認です）。 サポート終了版（8.1 以前）を使っている場合は、PHP 本体からは原則として更新が来ません。今回のソースパッチは例外措置と考えて、サポート版へ移行するか、LTS を提供するベンダーと契約するのが現実的です。 まとめ 今回の PHP の公表で異例だったのは、「予告したこと」そのものより、「予告とリリースを前倒しせざるを得ない状況（情報流出）になったこと」と、「その手前で修正が2年近く止まっていたこと」だと私は読んでいます。 次に PHP が「Upcoming Critical PHP Security Release」のような予告を出したとき、それは普段の「定例日の木曜に黙って出す」流れとは違う扱いのサインです。そのときに慌てないよう、購読先を1つ増やしておくのをおすすめします。 この記事の根拠は主に PHP の公式ポリシー文書、php.net の記録、PHP Foundation のブログ、各社の公開発言で、一覧を下に置きます。 参考リンク php-src SECURITY.md php/policies: security-classification php/policies: release-process php.net: Upcoming Critical PHP Security Release（10月20日の予告） php.net: Security advisory（10月21日） PHP Foundation: How the PHP Security Team Handles Your Reports（10月14日） PHP Foundation: Today's PHP Security Release and How We're Strengthening PHP Security（10月21日） PHP Foundation: PHP Core Security Audit Results（2024年監査） php.announce アーカイブ OpenAI: Coordinated disclosure of critical vulnerabilities Alexandre Daubois: CVE-2026-109618 php2shell rumors The Record: PHP Foundation statement（Chainguard CEO の談話） The Next Web: PHP Foundation structural failure OpenSSL Security Policy Django Security Policies curl Security Process Rails Security Policy Linux kernel: Security bugs 前回の記事: 修正版は23時のはずが22時31分に出ていた。php2shell で日本の約200組織が止まった夜の時系列

@Dev_emmy11 You are welcome, read more about it here: channels_redis#422

MySQL UNSIGNED Primary Keys: Signed or Unsigned, and How to Fix Error 3780 When They Mix --- title: "MySQL UNSIGNED Primary Keys: Signed or Unsigned, and How to Fix Error 3780 When They Mix" published: true description: "Schemity tested signed vs UNSIGNED keys on MySQL 8.4: same storage, but a mismatched foreign key fails with error 3780. Which to pick, and what a switch..." canonical_url: schemity.com cover_image: schemity.com tags: mysql, database, laravel, sql --- Disclosure: I build Schemity, a desktop ERD tool - this post is from our blog and uses it for the examples. > TL;DR: Use the sign your schema already uses, because MySQL refuses a foreign key whose sign differs from the key it references. Laravel and WordPress create BIGINT UNSIGNED keys, while Rails and Django create signed BIGINT. For a new schema with nothing deciding for you, signed BIGINT is simpler, and changing the sign later means dropping every foreign key that references the key and copying the key's table and each referencing table. A MySQL primary key can be signed or UNSIGNED, and both cost the same storage: 4 bytes for INT, 8 for BIGINT. What decides the choice is not the range but the foreign keys. MySQL refuses any foreign key whose sign differs from the column it references, so the sign of the first key decides the sign of every key column that will ever point at it. That is why the question usually arrives as an error rather than a design discussion. A migration adds an orders table to a Laravel database, declares user_id as a plain BIGINT, and MySQL answers with error 3780. Below is what each choice gets you, measured on MySQL 8.4.11 in a throwaway container, how to pick, and what changing your mind later costs. What UNSIGNED changes in a MySQL integer column Only the range. The MySQL manual's integer table gives each type one storage size, signed or not: | Type | Bytes | Signed maximum | UNSIGNED maximum | | --- | --- | --- | --- | | INT | 4 | 2,147,483,647 | 4,294,967,295 | | BIGINT | 8 | 9,223,372,036,854,775,807 | 18,446,744,073,709,551,615 | On 8.4, inserting 2,147,483,648 into a signed INT and -1 into an INT UNSIGNED both failed with ERROR 1264: Out of range value. The doubled range is real for INT, where 2.1 billion is a number a busy events table can reach. For BIGINT it is academic: inserting a million rows a second, a signed key lasts about 292,000 years. Two things UNSIGNED is not. It is not deprecated on integers: MySQL 8.0.17 deprecated it for DECIMAL, FLOAT and DOUBLE, and 8.4 still warns UNSIGNED for decimal and floating point data types is deprecated on those, but not on INT or BIGINT. And the (20) in BIGINT(20) UNSIGNED is a display width, deprecated in the same release. 8.4 warns Integer display width is deprecated and SHOW CREATE TABLE drops it. Which frameworks create signed and unsigned keys Most schemas did not choose. The framework that created the first table did: | Framework | Primary key it creates on MySQL | Source | | --- | --- | --- | | Laravel | BIGINT UNSIGNED from $table->id(), and foreignId() matches it | Laravel 13 migrations docs: id is "an alias of the bigIncrements method", which "creates an auto-incrementing UNSIGNED BIGINT" | | WordPress | bigint(20) unsigned on every core table: ID, post_author, comment_ID | wp-admin/includes/schema.php | | Rails | signed bigint | primary_key: "bigint auto_increment PRIMARY KEY" in the MySQL adapter | | Django | signed bigint for BigAutoField, integer for AutoField | "BigAutoField": "bigint AUTO_INCREMENT" in the MySQL backend | Laravel's current helpers stay consistent with each other, but its default changed once. Apps created before Laravel 5.8 made users.id with $table->increments('id'), which is INT UNSIGNED, so a new foreignId('user_id') pointing at it fails, and the column needs $table->unsignedInteger('user_id') instead. WordPress core declares no foreign keys at all, so there the error comes from a plugin or a hand-written table. Otherwise the problem appears where two conventions meet: a hand-written $table->bigInteger('user_id') (Laravel's bigInteger is signed) next to $table->id(), a Laravel app added on top of a database a Rails or raw-SQL app created first, or a reporting table written by hand in a WordPress database. To see which convention a database already uses, count its integer primary keys by type: SELECT c.COLUMN_TYPE, COUNT(*) AS primary_keys FROM information_schema.COLUMNS c JOIN information_schema.KEY_COLUMN_USAGE k ON k.TABLE_SCHEMA = c.TABLE_SCHEMA AND k.TABLE_NAME = c.TABLE_NAME AND k.COLUMN_NAME = c.COLUMN_NAME AND k.CONSTRAINT_NAME = 'PRIMARY' WHERE c.TABLE_SCHEMA = DATABASE() AND c.DATA_TYPE IN ('tinyint', 'smallint', 'mediumint', 'int', 'bigint') GROUP BY c.COLUMN_TYPE; One row back means one convention. Two rows means the next foreign key has to check which table it points at. Should a MySQL primary key be UNSIGNED? Only when the schema around it already is. The sign is not a per-table preference, it is a convention the whole schema has to share, and the cheapest choice is almost always the one already in the database: | Situation | Choose | Why | | --- | --- | --- | | Laravel or WordPress schema | BIGINT UNSIGNED, on keys and every foreign key column | Every existing key is unsigned, and a signed column cannot reference one | | Rails or Django schema | Signed BIGINT | Same rule, the other way round | | New schema, no framework deciding | Signed BIGINT | The extra range is unreachable, and signed avoids the problems below | | A 4-byte INT key that may pass 2.1 billion | BIGINT, unless key size really matters | INT UNSIGNED doubles the headroom once, BIGINT multiplies it by four billion | The case for signed in a new schema is everything outside the table. Subtraction is one: on 8.4, SELECT a.id - b.id between two BIGINT UNSIGNED ids, 1 minus 2, failed with ERROR 1690: BIGINT UNSIGNED value is out of range, and CAST(a.id - b.id AS SIGNED) failed the same way, because the subtraction runs first. Only CAST(a.id AS SIGNED) - CAST(b.id AS SIGNED), or the NO_UNSIGNED_SUBTRACTION SQL mode, returned -1. Porting is another: PostgreSQL, SQL Server and SQLite have no unsigned 64-bit integer, so a BIGINT UNSIGNED key either moves to BIGINT with a theoretical upper half it can no longer hold, or to NUMERIC. Java's long, Go's int64 and PHP's int are signed 64-bit as well, so a BIGINT UNSIGNED value above 2^63-1 has no native type in them. Whichever you pick, the rule that is not optional is the same: a foreign key column gets exactly the type of the key it references, UNSIGNED included. Why does MySQL return error 3780 for a foreign key? Because the MySQL manual requires it: "The size and sign of fixed precision types such as INTEGER and DECIMAL must be the same." This is the Laravel case, reproduced on 8.4.11: CREATE TABLE users ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, email VARCHAR(255) NOT NULL ); CREATE TABLE posts ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT NOT NULL, CONSTRAINT posts_user_id_foreign FOREIGN KEY (user_id) REFERENCES users (id) ); ERROR 3780 (HY000): Referencing column 'user_id' and referenced column 'id' in foreign key constraint 'posts_user_id_foreign' are incompatible. Three details from the same test session: - SET foreign_key_checks = 0 does not get it through. The same CREATE TABLE failed with the same error. The manual lists "creating a foreign key constraint where a column references a nonmatching column type" among the things disabled checks still refuse. - Same sign, different size fails too. An INT UNSIGNED column referencing BIGINT UNSIGNED returned 3780 as well. - The direction does not matter. A BIGINT UNSIGNED column referencing a signed BIGINT key failed the same way as the reverse. So did a Laravel foreignId('account_id'), which differs in both size and sign, pointing at a legacy signed INT key. The fix there is $table->integer('account_id') followed by $table->foreign('account_id')->references('id')->on('accounts'), matching the key rather than the framework's default. The other cause of error 3780: string keys with different collations Integer keys are not the only ones. The same manual passage goes on: "For nonbinary (character) string columns, the character set and collation must be the same." A CHAR(36) UUID key or a country_code hits this whenever the key's table and the new table were created under different defaults, and the error text is identical, so it reads like a type problem. It usually comes from a table made years ago. A database created on MySQL 8.0 or later defaults to utf8mb4_0900_ai_ci, the 8.0 server default. Older tables often carry utf8mb3_general_ci, and Laravel's default config writes utf8mb4_unicode_ci onto every table its migrations create. Any table made without a COLLATE clause, by raw SQL, a GUI or another framework, gets the database default instead. On 8.4.11: CREATE TABLE tenants ( id CHAR(36) NOT NULL PRIMARY KEY, name VARCHAR(100) NOT NULL ) DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci; CREATE TABLE projects ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, tenant_id CHAR(36) NOT NULL, CONSTRAINT projects_tenant_id_foreign FOREIGN KEY (tenant_id) REFERENCES tenants (id) ); ERROR 3780 (HY000): Referencing column 'tenant_id' and referenced column 'id' in foreign key constraint 'projects_tenant_id_foreign' are incompatible. Both columns are utf8mb4 and both are CHAR(36). Only the collation differs, and foreign_key_checks = 0 did not get it through either. Declaring the column as tenant_id CHAR(36) COLLATE utf8mb4_unicode_ci NOT NULL did. To see which keys a new table will fail against before you write one, list the primary key columns whose collation differs from the database default: SELECT c.TABLE_NAME, c.COLUMN_NAME, c.COLLATION_NAME, s.DEFAULT_COLLATION_NAME AS new_tables_get FROM information_schema.COLUMNS c JOIN information_schema.KEY_COLUMN_USAGE k ON k.TABLE_SCHEMA = c.TABLE_SCHEMA AND k.TABLE_NAME = c.TABLE_NAME AND k.COLUMN_NAME = c.COLUMN_NAME AND k.CONSTRAINT_NAME = 'PRIMARY' JOIN information_schema.SCHEMATA s ON s.SCHEMA_NAME = c.TABLE_SCHEMA WHERE c.TABLE_SCHEMA = DATABASE() AND c.COLLATION_NAME <> s.DEFAULT_COLLATION_NAME; Every row is a key whose foreign key columns need an explicit COLLATE. Integer keys never appear, because their COLLATION_NAME is NULL. What does it cost to change a key from UNSIGNED to signed? More than one ALTER. With orders.user_id referencing users.id, both BIGINT UNSIGNED, the obvious migration fails on its first statement, with or without foreign key checks: ALTER TABLE users MODIFY COLUMN id BIGINT AUTO_INCREMENT; ERROR 3780 (HY000): Referencing column 'user_id' and referenced column 'id' in foreign key constraint 'orders_user_id_foreign' are incompatible. Changing the child column first fails the same way. The foreign key has to go, both columns change, and the key comes back: ALTER TABLE orders DROP FOREIGN KEY orders_user_id_foreign; ALTER TABLE users MODIFY COLUMN id BIGINT AUTO_INCREMENT; ALTER TABLE orders MODIFY COLUMN user_id BIGINT NOT NULL; ALTER TABLE orders ADD CONSTRAINT orders_user_id_foreign FOREIGN KEY (user_id) REFERENCES users (id); On two tables of 1,000,000 rows each, that took 7.3 seconds, and every statement after the drop reported 1,000,000 rows affected: | Statement | Time | What MySQL did | | --- | --- | --- | | DROP FOREIGN KEY | 0.01 s | Metadata only | | MODIFY COLUMN on users.id | 1.53 s | Copied the table: a type change is ALGORITHM=COPY only | | MODIFY COLUMN on orders.user_id | 2.49 s | Copied the table | | ADD CONSTRAINT ... FOREIGN KEY | 3.27 s | Copied orders again: with checks on, ALGORITHM=INPLACE is refused with Adding foreign keys needs foreign_key_checks=OFF | The last step is the one you can make cheaper. With foreign_key_checks = 0, the same ADD CONSTRAINT on a 1,000,000-row table affected 0 rows and took 0.87 s, because MySQL skips checking the existing rows. That is safe here, where only the type changed and no value did, and unsafe anywhere rows might not match. The online DDL table lists a column type change as "Permits Concurrent DML: No", so writes to each table wait while it is copied. Multiply by every table that references users and the cost of switching is clear: decide the sign once, at the first table, because changing it later is a schema migration across the whole graph of foreign keys. How Schemity handles UNSIGNED keys Schemity is database design software that reads your live database, shows the impact of every schema change before it runs, and keeps the diagram as a file in Git. When you connect to MySQL, it reads each column's sign with its type, and the canvas shows an unsigned column as uBIGINT, so a schema's convention, and any table that breaks it, is visible at a glance across the diagram. !Schemity's canvas reading the users, orders and accounts tables from MySQL 8.4: users.id, orders.id and orders.user_id are uBIGINT, the u prefix marking BIGINT UNSIGNED, while accounts.id and orders.account_id are plain INTEGER; orders.total is DECIMAL(10,2) with a 0.00 default, users.created_at is a nullable TIMESTAMP marked N, and orders shows idx: 2 for the two foreign key indexes To design the next one, the field editor offers an Unsigned checkbox on MySQL integer types. Drawing a relationship copies the key's full type into the new foreign key column, sign included, so a new table pointing at a Laravel users.id gets user_id BIGINT UNSIGNED and its migration applies instead of failing with 3780. A string key's character set and collation are copied the same way, so a column pointing at tenants.id is created with COLLATE utf8mb4_unicode_ci whatever the database default is. Ticking or clearing Unsigned on a key changes every foreign key column that references it in the same edit, one undo step. !Schemity's canvas with a new posts table drafted beside the users, orders and accounts tables read from MySQL, drawn with a dashed border because it is not yet in the database: the relation from users gave posts.user_id the type uBIGINT, matching users.id, and posts.id was set to uBIGINT with the Unsigned checkbox so the new table follows the schema's unsigned convention !Schemity's Findings drawer previewing the new posts table: the planned migration creates posts with id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY and user_id BIGINT UNSIGNED NOT NULL, with the constraint posts_user_id_fkey FOREIGN KEY (user_id) REFERENCES users (id), so the column matches users.id and MySQL accepts the key; Planned changes lists Table posts created, and the only lint finding is the unrelated users.created_at A mismatch that already exists, or one you type yourself, is caught by lint. Its fk-type-mismatch rule reports "Foreign key type does not match the column it references" on the foreign key's field row, with the detail that the two columns differ in UNSIGNED and that MySQL refuses a foreign key whose column and key differ in sign. On MySQL it also names both collations when a string key and its foreign key column differ in character set or collation. !Schemity's Lint drawer beside the canvas after Unsigned was cleared on posts.user_id: under Fails at runtime, the finding on posts.user_id reads Foreign key type does not match the column it references, with the detail posts.user_id and users.id differ in UNSIGNED. MySQL refuses a foreign key whose column and key differ in sign; a red bar in the margin marks the posts.user_id row, now BIGINT against users.id uBIGINT, and a second finding under Convention notes that users.created_at is nullable with no default Changing the sign of a key that is already referenced plans the drop, modify, re-add sequence from the section above, and impact analysis reports each MODIFY before it runs as "Rewrites users to change id" with the row count, and a sign change that can lose values, such as BIGINT UNSIGNED to BIGINT, as "values may not survive the cast". The foreign key it adds back is listed too, as "Checking the new key holds back writes to orders", since that was the slowest step in the test above. If you later convert the diagram's SQL to PostgreSQL, SQL Server or SQLite, unsigned integers are widened one size so every value still fits, and a BIGINT UNSIGNED stays a signed 64-bit integer (BIGINT, or INTEGER on SQLite) with a note that values above 2^63-1 would not. !Schemity's Findings drawer after Unsigned was cleared on users.id, with orders.user_id following it to BIGINT on the canvas: the planned migration drops the foreign key orders_user_id_foreign, modifies orders.user_id and users.id to BIGINT with foreign key checks off, then adds the constraint back with checks on; Impact lists both columns as values may not survive the cast, Rewrites orders to change user_id and Rewrites users to change id at about 199.6K rows each, writes held back on both tables while they are copied and while the new key is checked, users.id reaching orders through orders_user_id_foreign, and Drops and recreates a foreign key on orders Signed or unsigned MySQL keys, in one list - Existing schema: use whatever sign its primary keys already have. The information_schema query above tells you. - Laravel or WordPress: BIGINT UNSIGNED everywhere, including hand-written foreign key columns. - Rails, Django, or a new schema with no framework: signed BIGINT. - Foreign key column: exactly the key's type, sign and size included. Error 3780 means it is not. - Changing the sign later: drop the foreign keys, modify both sides, add the keys back, and expect each table to be copied. The same choice on PostgreSQL, where there is no unsigned option but the key can be a UUID, is in UUID vs bigint primary keys in Postgres. Naming the constraints those foreign keys create, so orders_user_id_foreign still means something in five years, is in the PostgreSQL constraint naming convention.

PostgreSQL Constraint Naming Convention: Which Pattern to Use, and How to Find the Names That Drifted --- title: "PostgreSQL Constraint Naming Convention: Which Pattern to Use, and How to Find the Names That Drifted" published: true description: "Schemity's guide to naming Postgres constraints and indexes: the pattern to pick, the 63-byte cut that makes two names collide, and a query for stale..." canonical_url: schemity.com cover_image: schemity.com tags: postgres, database, sql, webdev --- Disclosure: I build Schemity, a desktop ERD tool - this post is from our blog and uses it for the examples. > TL;DR: Use the pattern PostgreSQL already uses for unnamed constraints, <table>_<columns>_<suffix> with pkey, key, fkey, check and idx, and write it out explicitly in migrations. Index-backed names are unique across the whole schema, not per table, and anything over 63 bytes is cut with only a NOTICE, which can turn two different names into one. The naming convention to use for PostgreSQL constraints is the one PostgreSQL already uses: <table>_<columns>_<suffix>, with pkey, key, fkey, check and idx as the suffixes. Write those names out explicitly in your migrations rather than leaving them to the default, and keep every one under 63 bytes. That sounds like a style choice, and most of it is. Three parts are not: index names share one namespace across the whole schema, a default name depends on what the database already contains, and a name longer than 63 bytes is cut without an error. Everything below was run on PostgreSQL 18.3 in a throwaway container, with the exact output where it matters. What PostgreSQL names a constraint when you don't Create a table without naming anything and read pg_constraint back: CREATE TABLE customers ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, email text NOT NULL UNIQUE ); CREATE TABLE orders ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, customer_id bigint NOT NULL REFERENCES customers, total numeric(12,2) NOT NULL CHECK (total >= 0), status text NOT NULL CHECK (status IN ('open', 'paid', 'shipped')) ); CREATE INDEX ON orders (customer_id); PostgreSQL picks customers_pkey, customers_email_key, orders_pkey, orders_customer_id_fkey, orders_total_check, orders_status_check and the index orders_customer_id_idx. On 18, each NOT NULL also appears as a named constraint, orders_total_not_null, because PostgreSQL 18 began storing NOT NULL in pg_constraint, which "allows names to be specified for NOT NULL constraint". An expression index such as CREATE INDEX ON orders (lower(status)) is named after the function, orders_lower_idx. The pattern is good. It puts the table first, so names sort together, and it says what kind of object each one is. The problem is the word "default": the name you get depends on the database at that moment. Run ALTER TABLE customers ADD UNIQUE (email) a second time and PostgreSQL does not complain. It creates a second unique constraint, and a second index, called customers_email_key1. A migration that adds an unnamed constraint can produce _key on staging and _key1 on a production database where an old one survived, and the next migration that drops it by name only works on one of them. Postgres constraint naming convention: which pattern should you use? Pick one pattern and spell it out in every migration. Four patterns are common, and a database that has passed through more than one tool usually holds all of them: | Who names it | Foreign key on orders.customer_id | Index on orders.customer_id | | --- | --- | --- | | PostgreSQL, when unnamed | orders_customer_id_fkey | orders_customer_id_idx | | Prisma | orders_customer_id_fkey | orders_customer_id_idx | | Django | <table>_customer_id_<8-character hash>_fk_customers_id | <table>_customer_id_<8-character hash> | | Rails | fk_rails_<10-character hash> | index_orders_on_customer_id | | Prefix style, hand-written | fk_orders_customers | idx_orders_customer_id | The PostgreSQL pattern wins for one practical reason: any constraint anybody creates without a name, in a hotfix, a console session or a tool you do not control, lands in that pattern anyway. Choosing it means the exceptions look like everything else. Prisma already writes it, and in Django every UniqueConstraint and CheckConstraint in Meta.constraints takes a required name, which is where you can write it. Whatever the framework generates for its own keys, two rules hold for the names you write by hand: - Put the table name in every name. Check and foreign key names are unique per table, so n_positive can exist on two tables. Primary keys, unique constraints and indexes are backed by an index, and an index is a relation, so its name must be unique across the whole schema. Name a unique constraint email_unique on a second table and PostgreSQL answers relation "email_unique" already exists. The same happens with two indexes called idx_email. - Name a CHECK after the rule, not the column. orders_total_check is fine while a column has one check. With two, orders_total_non_negative and orders_total_max tell you which one an error refers to. What happens to a constraint name over 63 bytes? PostgreSQL keeps the first 63 bytes. The manual states it plainly: "The system uses no more than NAMEDATALEN-1 bytes of an identifier; longer names can be written in commands, but they will be truncated." It is 63 bytes, not characters, so a name with non-ASCII letters runs out sooner. For its own default names PostgreSQL is careful. It shortens the table and column parts and keeps the suffix, so an unnamed unique constraint on the column below becomes subscription_billing_adjustme_customer_account_reference_nu_key, exactly 63 bytes. An explicit name gets no such care. It is cut at byte 63 wherever that falls, which on a long name is usually inside the suffix, and the only signal is a NOTICE: CREATE TABLE subscription_billing_adjustments_v2 ( id int PRIMARY KEY, customer_account_reference_number text ); ALTER TABLE subscription_billing_adjustments_v2 ADD CONSTRAINT subscription_billing_adjustments_v2_customer_account_reference_number_key UNIQUE (customer_account_reference_number); -- NOTICE: identifier "subscription_billing_adjustments_v2_customer_account_reference_number_key" -- will be truncated to "subscription_billing_adjustments_v2_customer_account_reference_" ALTER TABLE subscription_billing_adjustments_v2 ADD CONSTRAINT subscription_billing_adjustments_v2_customer_account_reference_number_check CHECK (customer_account_reference_number <> ''); -- NOTICE: identifier "subscription_billing_adjustments_v2_customer_account_reference_number_check" -- will be truncated to "subscription_billing_adjustments_v2_customer_account_reference_" -- ERROR: constraint "subscription_billing_adjustments_v2_customer_account_reference_" -- for relation "subscription_billing_adjustments_v2" already exists Two different names became one, and the check was never created. An index named with the _idx version fails the same way, as relation ... already exists. When the cut falls a little later, inside the suffix but after the names have already diverged, nothing fails at all: a _key and a _fkey on a 61-byte prefix are both created, as names ending in _k and _f, which match nothing in your migration files. PostgreSQL applies the same cut when you look a name up, so DROP CONSTRAINT with the long name still works. What breaks is anything that compares the name it wrote with the name the catalogue holds, and anyone reading an error message that names a constraint ending in _reference_. The fix is to shorten the middle, not the end. Abbreviate the table or column part until the whole name fits, or shorten it and add a short hash, the way Django builds its generated names, so two long names cannot end up as one. Renaming a table does not rename its constraints ALTER TABLE orders RENAME TO purchases renames the table and nothing else. Every constraint keeps its old name, and the next error reads like a contradiction: ERROR: new row for relation "purchases" violates check constraint "orders_total_check" Rename them in the same migration. ALTER TABLE purchases RENAME CONSTRAINT orders_total_check TO purchases_total_check works for every kind of constraint, and for a primary key or unique constraint it renames the index behind it too. Plain indexes and the identity sequence keep their old names as well: rename them with ALTER INDEX orders_customer_id_idx RENAME TO purchases_customer_id_idx and ALTER SEQUENCE orders_id_seq RENAME TO purchases_id_seq. To find the names that have already drifted, list every constraint whose name does not start with its own table's name: SELECT c.conrelid::regclass AS table_name, c.conname FROM pg_constraint c JOIN pg_class t ON t.oid = c.conrelid JOIN pg_namespace n ON n.oid = t.relnamespace WHERE n.nspname = 'public' AND c.contype IN ('p', 'u', 'f', 'c') AND c.conname NOT LIKE t.relname || '\_%'; Run against the tables above after the rename, it returned four rows, all on purchases: orders_pkey, orders_customer_id_fkey, orders_total_check and orders_status_check. On PostgreSQL 18, add 'n' to the list to include the NOT NULL names, which go stale the same way. The query reads only constraints in public, so plain indexes need a look of their own. It also lists names that are not drift: every fk_rails_ key in a database built by Rails, and PostgreSQL's own defaults on a table long enough that the table part was shortened. Read the list before renaming anything. How Schemity names constraints and indexes Schemity is database design software that reads your live database, shows the impact of every schema change before it runs, and keeps the diagram as a file in Git. When you connect to PostgreSQL, the constraints are read with the names the database holds, whichever tool wrote them, so a mixed database shows its mixed conventions rather than a tidied copy. When you design the next one, a key, constraint or index you draw is named in the PostgreSQL pattern: orders_pkey, orders_customer_id_fkey, customers_email_key, orders_customer_id_idx, and for a check, the table, the rule name you type and _check, such as orders_total_non_negative_check. A composite unique constraint is named the same way, after all of its columns. When a name would pass 63 bytes, Schemity cuts the table and column part from its end, adds a short hash of the full name, and keeps the suffix, so the long example above becomes subscription_billing_adjustments_v2_customer_accoun_1jp0itp_key and its check subscription_billing_adjustments_v2_customer_accou_giumie_check, both exactly 63 bytes. The name in the diagram, the name in the planned SQL and the name in the database are the same string, and two names that agree up to the cut stay apart. !Schemity's Findings drawer for a new table subscription_billing_adjustments_v2 with an id BIGINT identity primary key and a unique TEXT column customer_account_reference_number: the planned migration creates the column with CONSTRAINT subscription_billing_adjustments_v2_customer_accoun_1jp0itp_key UNIQUE, a 63-byte name ending in a short hash and the _key suffix, with Lint 0 and Impact 0 The constraint names are fitted, but a table or column name is whatever you type, and PostgreSQL cuts those at 63 bytes too. The lint rule identifier-too-long reports one before the migration runs, saying the name "is longer than PostgreSQL keeps", with its byte count. On MySQL, where the limit is 64 characters, and SQL Server, at 128, the same rule says the statement that creates it fails, because those databases refuse the name instead of cutting it. !Schemity's Lint drawer on a diagram connected to PostgreSQL 18.3: under Costs, the column purchases.customer_account_reference_number_imported_from_the_legacy_billing_system is reported as Name is longer than PostgreSQL keeps, at 73 bytes against the 63 PostgreSQL keeps; above it, the foreign key on purchases.customer_id appears under its database name fk_rails_e74ce85cbc with the finding that it has no index, and a Convention finding notes the new column is a nullable string The convention, in one list - Pattern: <table>_<columns>_<suffix>, with pkey, key, fkey, check, idx, the same as PostgreSQL's defaults. - Name every constraint explicitly in migrations, so every database gets the same name. - Put the table name first in every name: index-backed names are unique across the schema. - Name a CHECK after its rule once a column has more than one. - Stay under 63 bytes; shorten the middle or add a hash, never let PostgreSQL cut the suffix. - Rename constraints and indexes in the same migration that renames the table, and run the drift query once on an old database. The same kind of decision for unique rules, constraint or index, is in unique constraint vs unique index in Postgres. Where a check's name ends up mattering most, in the allowed values of a column, is covered in enum vs check constraint vs lookup table.