Re: [sqlite] Foreign key error...

Ken Wagner Sun, 08 Jan 2017 04:05:47 -0800

Keith,

Ahh, better to be specific and avoid simplistic assumptions.

For foreign keys which is better: 'trackerartist' or 'artistid' in thetrack file? Does it matter? Is it personal preference?


Ken


On 01/08/2017 05:46 AM, Keith Medcalf wrote:

... join ... using (column) has nothing whatever to do with foreign keys.

"FROM a JOIN b USING (c) is "syntactic sugar" (meaning that it is sweetening and full of 
calories, but has no nutritional value) for the expression "FROM a, b WHERE a.c = b.c"

This is so, for example, if you use really long stupid names it saves 
considerable space and typing:

SELECT * FROM TheFirstTableToBeJoined JOIN TheSecondTableToBeJoined USING 
(TheCommonColumnNameBetweenTheFirstAndTheSecondTable);
-vs-
SELECT * FROM TheFirstTableToBeJoined, TheSecondTableToBeJoined WHERE 
TheFirstTableToBeJoined.TheCommonColumnNameBetweenTheFirstAndTheSecondTable = 
TheSecondTableToBeJoined.TheCommonColumnNameBetweenTheFirstAndTheSecondTable;

-----Original Message-----
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
On Behalf Of Ken Wagner
Sent: Sunday, 8 January, 2017 04:04
To: SQLite mailing list
Subject: Re: [sqlite] Foreign key error...

Thanks, Kees,

The message is helpful as a warning.

select artistname, trackname from artist inner join track on
trackartist = artistid;  works just fine.

But isn't the efficiency of 'using (artistid)' more desirable?

Is the use of a 'trackerartist' as the foreign key used because it is
more informative?

I.e., wherever it is seen it shows the track-artist link? But is more
demanding when coding:

      'on trackerartist = artistid' vs 'using (artistid)'

Best or preferred SQLite3 practice for using which foreign reference
style 'trackartist' vs 'artistid'?

Thanks,
Ken

On 01/08/2017 04:47 AM, Kees Nuyt wrote:

On Sun, 8 Jan 2017 04:21:16 -0600, Ken Wagner
<beauco...@gmail.com> wrote:

Hello SQLusers,

The error below occurs even though the

CREATE TABLE track(

    trackid     INTEGER,
    trackname   TEXT,
    trackartist INTEGER,
    *FOREIGN KEY(trackartist) REFERENCES artist(artistid)*
);

statement at https://sqlite.org/foreignkeys.html was observed.

It appears that 'trackerartist' should be named 'artistid'.

SQLite3 CLI results using version SQLite 3.15.2 2016-11-28 19:13:37
bbd85d235f7037c6a033a9690534391ffeacecc8

sqlite> select artistname, trackname from artist inner join track using
(trackartist);
Error:\ cannot join using column trackartist - column not present in
both tables
sqlite> .tables track
track
sqlite> .schema track
CREATE TABLE track(
    trackid     INTEGER,
    trackname   TEXT,
    trackartist INTEGER,
    FOREIGN KEY(trackartist) REFERENCES artist(artistid)
);
sqlite> .schema artist
CREATE TABLE artist(
    artistid    INTEGER PRIMARY KEY,
    artistname  TEXT

Am I missing something important here?

The error message is quite informative: the artist table does
not have a column trackartist.

Try:
select artistname, trackname from artist inner join track on
trackartist = artistid;

HTH

_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Foreign key error...

Reply via email to