Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

incorrect sqlite syntax for composite primary key #3500

Open
K6TD opened this Issue Mar 15, 2019 · 1 comment

Comments

Projects
None yet
2 participants
@K6TD
Copy link

commented Mar 15, 2019

Details

Sqlite seems to require an attribute style syntax for creating a composite (multi-column) primary key. Here is the syntax generated:

...
CREATE TABLE xxxx (
    PN            TEXT    REFERENCESitem_master (PN)
    mfg_ID            INTEGER NOT NULL
                              DEFAULT (48) 
                              REFERENCES manufacturer_ID (ID) 
                              PRIMARY KEY,
    [mfg name]        TEXT,
    [mfg part number] TEXT    PRIMARY KEY,
);
...
PRAGMA foreign_keys = 1;

SQlite supports:

...
CREATE TABLE...

PRIMARY (PN, [mfg part number])

};

Steps to reproduce

from the table structure view, select two columns to be primary key.

Operating system

macOS 10.14.3 (Mojave)

SQLiteStudio version

V3.2.1

@pawelsalawa

This comment has been minimized.

Copy link
Owner

commented Mar 16, 2019

Instead of marking two columns as primary key, you should create normal (not PK) columns and then on the bottom of Structure tab there is frame for table's global constraints, where you can create PK for multiple columns.

This way you will generate proper DDL.

I will keep this issue to track a change, where SQLiteStudio will not allow you yo select 2 columns as PK and will instruct you to do this the way I just described above.

@pawelsalawa pawelsalawa added the bug label Mar 16, 2019

@pawelsalawa pawelsalawa added this to the 3.2.2 milestone Mar 16, 2019

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.