Creating table with foreign keys

Question

Im trying to create a table programmatically in sqlite. But it gives me an syntacs error when im trying to create a foreign key constraint. Can anybody tell me why.

this is my code:

CREATE TABLE tblUser (ID INT64 Primary Key, Name NVARCHAR(100) NOT NULL, EMail
NVARCHAR(100) NOT NULL); 

CREATE TABLE tblMachine (ID INT64 Primary Key, Name NVARCHAR(100) NOT NULL,
ProcessorID NVARCHAR(100) NOT NULL, BiosID NVARCHAR(100) NOT NULL);

CREATE TABLE tblLicenseInfo (ID INT64 Primary Key, UserID INT64 NOT NULL,
MachineID INT64 NOT NULL, ExpirationDate DATETIME NOT NULL, DateOfChange DATETIME
NOT NULL, LicenseKey NVARCHAR(100) NOT NULL),

FOREIGN KEY (UserID) REFERENCES tblUser.ID,
FOREIGN KEY (MachineID) REFERENCES tblMachine.ID;

thank you very much.

O. R. Mapper · Accepted Answer · 2014-05-02 07:56:37Z

1

You need to include the FOREIGN KEY clauses within the parenthesis of the CREATE TABLE commands. Have a look at the CREATE TABLE syntax diagram, where a FOREIGN KEY clause is a table constraint.

Moreover, the referenced column(s) of the other table need(s) to be indicated in parenthesis, not with the dot notation. This is described in the syntax diagram for the FOREIGN KEY clause.

Hence, your CREATE TABLE statement should look like this:

CREATE TABLE tblLicenseInfo (
    ID INT64 PRIMARY KEY,
    UserID INT64 NOT NULL,
    MachineID INT674 NOT NULL,
    ExpirationDate DATETIME NOT NULL,
    DateOfChange DATETIME NOT NULL,
    LicenseKey NVARCHAR(100) NOT NULL,
    FOREIGN KEY (UserID) REFERENCES tblUser (ID),
    FOREIGN KEY (MachineID) REFERENCES tblMachine (ID)
);

answered May 2, 2014 at 7:56

O. R. Mapper

20.9k11 gold badges76 silver badges128 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user3292642 Over a year ago

thank you very much, but it still says "near "tblUser": syntax error" any ideas?

O. R. Mapper Over a year ago

@user3292642: Right now, I'm not seeing any further problem after comparing with the syntax diagrams step by step. To be clear, are you trying to run the above commands (from your question) one by one?

user3292642 Over a year ago

had a spelling mistake, its working fine now, thanks

ssbb · Accepted Answer · 2014-05-02 07:56:04Z

0

you need an option to do it : in php try

$db = new SQLite3("../SQLLite/test.db");
$db->exec('PRAGMA foreign_keys = ON');

there are other way in commande for exemple

sqlite> PRAGMA foreign_keys = ON;

source : https://www.sqlite.org/foreignkeys.html

answered May 2, 2014 at 7:56

ssbb

1,9312 gold badges22 silver badges45 bronze badges

3 Comments

user3292642 Over a year ago

Im using visual studio and c#, do i need to set some property to true or on aswell?

ssbb Over a year ago

@user3292642 i'm the worst guy of c# in the world, can't say, sorry

user3292642 Over a year ago

okay you dont have to, at least its working now for me and i didnt do something like this

Collectives™ on Stack Overflow

Creating table with foreign keys

2 Answers 2

3 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related