{"action":"opened","issue":{"url":"https://api.github.com/repos/ondras/wwwsqldesigner/issues/97","labels_url":"https://api.github.com/repos/ondras/wwwsqldesigner/issues/97/labels{/name}","comments_url":"https://api.github.com/repos/ondras/wwwsqldesigner/issues/97/comments","events_url":"https://api.github.com/repos/ondras/wwwsqldesigner/issues/97/events","html_url":"https://github.com/ondras/wwwsqldesigner/issues/97","id":67096977,"number":97,"title":"SQLite: Invalid SQL generated for multi-column primary keys","user":{"login":"ondras","id":192172,"avatar_url":"https://avatars.githubusercontent.com/u/192172?v=3","gravatar_id":"","url":"https://api.github.com/users/ondras","html_url":"https://github.com/ondras","followers_url":"https://api.github.com/users/ondras/followers","following_url":"https://api.github.com/users/ondras/following{/other_user}","gists_url":"https://api.github.com/users/ondras/gists{/gist_id}","starred_url":"https://api.github.com/users/ondras/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/ondras/subscriptions","organizations_url":"https://api.github.com/users/ondras/orgs","repos_url":"https://api.github.com/users/ondras/repos","events_url":"https://api.github.com/users/ondras/events{/privacy}","received_events_url":"https://api.github.com/users/ondras/received_events","type":"User","site_admin":false},"labels":[{"url":"https://api.github.com/repos/ondras/wwwsqldesigner/labels/bug","name":"bug","color":"fc2929"},{"url":"https://api.github.com/repos/ondras/wwwsqldesigner/labels/imported","name":"imported","color":"FFFFFF"},{"url":"https://api.github.com/repos/ondras/wwwsqldesigner/labels/Priority-Medium","name":"Priority-Medium","color":"FFFFFF"}],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":0,"created_at":"2015-04-08T10:16:30Z","updated_at":"2015-04-08T10:16:30Z","closed_at":null,"body":"_From [NikitaTh...@gmail.com](https://code.google.com/u/112475953030429080148/) on March 24, 2010 04:46:07_\n\nWhen I create a table with a multi-column (composite) primary key, WWW SQL\r\nDesigner generates SQL that SQLite won't accept. For example --\r\n\r\nsqlite> CREATE TABLE foo (\r\n   ...> field1 INTEGER NOT NULL PRIMARY KEY ,\r\n   ...> field2 INTEGER NOT NULL PRIMARY KEY \r\n   ...> );\r\nSQL error: table \"foo\" has more than one primary key\r\n\r\nThe correct syntax is this --\r\n\r\nCREATE TABLE foo (\r\nfield1 INTEGER NOT NULL,\r\nfield2 INTEGER NOT NULL,\r\nPRIMARY KEY (field1, field2)  \r\n);\r\n\r\nAFAICT that's the only way to express a composite primary key. \r\n\r\nHere's where it gets tricky.\r\n\r\nIn issue #65 I pointed out how AUTOINCREMENT can only be used following a\r\nPRIMARY KEY constraint. In other words, the only permissible syntax for\r\nAUTOINCREMENT primary keys differs from the syntax for composite primary keys:\r\n\r\nCREATE TABLE foo (\r\nid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT\r\n);\r\n\r\nFor single-column, non-AUTOINCREMENT keys, either syntax is acceptable:\r\nCREATE TABLE foo (\r\nid INTEGER NOT NULL PRIMARY KEY \r\n);\r\n\r\nCREATE TABLE foo (\r\nid INTEGER NOT NULL,\r\nPRIMARY KEY (id)\r\n);\r\n\r\nAUTOINCREMENT is not permitted on multi-column keys, so we can ignore that\r\ncase. (The SQLite docs say, \"Multi-column primary keys do not become\r\naliases for the rowid. The AUTOINCREMENT keyword only works on a column\r\nthat is an alias for the rowid.\" http://www.sqlite.org/lang_createtable.html )\r\n\r\n\r\nIn short, it looks like one syntax is needed for AUTOINCREMENT primary keys\r\nand another for composite primary keys. If someone attempts to create a\r\ntable that applies AUTOINCREMENT to one of the columns of a multi-column\r\nprimary key, it doesn't matter which syntax you use because SQLite won't\r\naccept it.\n\n**Attachment:** [invalid_example.xml](http://code.google.com/p/wwwsqldesigner/issues/detail?id=90)\n\n_Original issue: http://code.google.com/p/wwwsqldesigner/issues/detail?id=90_"}}