{"action":"created","issue":{"url":"https://api.github.com/repos/zzzeek/sqlalchemy/issues/252","repository_url":"https://api.github.com/repos/zzzeek/sqlalchemy","labels_url":"https://api.github.com/repos/zzzeek/sqlalchemy/issues/252/labels{/name}","comments_url":"https://api.github.com/repos/zzzeek/sqlalchemy/issues/252/comments","events_url":"https://api.github.com/repos/zzzeek/sqlalchemy/issues/252/events","html_url":"https://github.com/zzzeek/sqlalchemy/pull/252","id":143795577,"number":252,"title":"Allow creating explicit non-clustered primary keys in MS SQL.","user":{"login":"frgtn","id":143999,"avatar_url":"https://avatars.githubusercontent.com/u/143999?v=3","gravatar_id":"","url":"https://api.github.com/users/frgtn","html_url":"https://github.com/frgtn","followers_url":"https://api.github.com/users/frgtn/followers","following_url":"https://api.github.com/users/frgtn/following{/other_user}","gists_url":"https://api.github.com/users/frgtn/gists{/gist_id}","starred_url":"https://api.github.com/users/frgtn/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/frgtn/subscriptions","organizations_url":"https://api.github.com/users/frgtn/orgs","repos_url":"https://api.github.com/users/frgtn/repos","events_url":"https://api.github.com/users/frgtn/events{/privacy}","received_events_url":"https://api.github.com/users/frgtn/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":1,"created_at":"2016-03-27T10:25:13Z","updated_at":"2016-03-28T00:09:36Z","closed_at":null,"pull_request":{"url":"https://api.github.com/repos/zzzeek/sqlalchemy/pulls/252","html_url":"https://github.com/zzzeek/sqlalchemy/pull/252","diff_url":"https://github.com/zzzeek/sqlalchemy/pull/252.diff","patch_url":"https://github.com/zzzeek/sqlalchemy/pull/252.patch"},"body":"TL; DR: Change `mssql_clustered=False` on `PrimaryKeyConstraint` to request a non-clustered primary key and change default to `None`, which uses default behaviour. This is required to have tables with clustered indexes on other than the primary key.\r\n\r\nReasoning:\r\nMS SQL Server default primary key creation (without `CLUSTERED` or `NONCLUSTERED` specified) depends on whether the table already has a clustered index. If a clustered index is present, it will create a non-clustered primary key. Without a clustered index (like when defining a new table), it would create a clustered primary key.\r\n\r\nPrior to this patch SQLAlchemy MS SQL dialect didn't allow specifying a non-clustered primary key, so in order to have a clustered index that isn't the primary key, one would have to create a table without a primary key, define a clustered index and then define the primary key. Otherwise MS SQL would create a clustered primary key and index creation would fail."},"comment":{"url":"https://api.github.com/repos/zzzeek/sqlalchemy/issues/comments/202170972","html_url":"https://github.com/zzzeek/sqlalchemy/pull/252#issuecomment-202170972","issue_url":"https://api.github.com/repos/zzzeek/sqlalchemy/issues/252","id":202170972,"user":{"login":"zzzeek","id":128223,"avatar_url":"https://avatars.githubusercontent.com/u/128223?v=3","gravatar_id":"","url":"https://api.github.com/users/zzzeek","html_url":"https://github.com/zzzeek","followers_url":"https://api.github.com/users/zzzeek/followers","following_url":"https://api.github.com/users/zzzeek/following{/other_user}","gists_url":"https://api.github.com/users/zzzeek/gists{/gist_id}","starred_url":"https://api.github.com/users/zzzeek/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/zzzeek/subscriptions","organizations_url":"https://api.github.com/users/zzzeek/orgs","repos_url":"https://api.github.com/users/zzzeek/repos","events_url":"https://api.github.com/users/zzzeek/events{/privacy}","received_events_url":"https://api.github.com/users/zzzeek/received_events","type":"User","site_admin":false},"created_at":"2016-03-28T00:09:36Z","updated_at":"2016-03-28T00:09:36Z","body":"very nicely done and this should be fine for 1.1.  I have a still long list of PRs for 1.1 that I'm trying to get to in order.  If I release 1.1beta and forget to put this in please ping me, but I'll put it in my list of TODO PRs."}}