SQLite Forum

Help with foreign key

Help with foreign key

(1) By anonymous on 2020-06-16 03:59:39 [link]

I have one table, A and will create table B

Both are autoincrement

Table A is called mysales and has a field called supplier

Table B will be called suppliers and will have a field called name

Table B's primary ket will be id and the foreign key will be supplier in table A

How can I write the CREATE TABLE statement correctly for table b

Thank you, 


(2.1) By Larry Brasfield (LarryBrasfield) on 2020-06-16 10:10:32 edited from 2.0 in reply to 1 [link]

This looks like a homework assignment, so I am going to give you a few tips without doing you (or your potential future coworkers) the disfavor of helping you gain credentials that do not reflect acquired skills.

First, you need to study the syntax of the language you will use for this assignment. An excellent depiction of it is here, [SQL As Understood By SQLite](https://sqlite.org/lang.html).

Second, you need to study [SQLite Foreign Key Support](https://sqlite.org/foreignkeys.html), where you will find not just how to effect foreign key relationships between tables but a good explanation of what foreign keys are for.

All of these web pages can be found via the [Permuted Index](https://sqlite.org/sitemap.html#pindex).

Once you have exhausted your self-directed study options, and have made a sincere attempt to write the SQL required for your assignment, if it does not quite work as **you intend**, you can bring your SQL here (to this forum) with specific questions, demonstrating your understanding or misunderstanding related to specific language features, and you will find a number of participants here willing to help you complete the learning that your homework is designed to promote.

(3) By anonymous on 2020-06-16 11:05:08 in reply to 2.1 [link]

Thank you for your response Larry.

Actually, I am creating this database for my little home business I started after getting laid off.  I wish it were a homework assignment.

(4) By Larry Brasfield (LarryBrasfield) on 2020-06-16 13:27:00 in reply to 3 [link]

Then see [CREATE TABLE](https://sqlite.org/lang_createtable.html), and be sure to expand the "column-def:", "column-constraint:" and "foreign-key-clause:" items.

Since you are designing your own schema, which will certainly be more detailed than what your 1st post stated, and probably ought to include even more tables, I still suggest reading up on what foreign keys do. You have decisions to make about [referential integrity](https://database.guide/what-is-referential-integrity/), including whether and/or how to achieve and enforce it.

I have to assume you are a programming neophyte, so I offer this advice which you will sooner or later appreciate was needed if you keep at it:

(1) Choose meaningful but short names (aka "identifiers") for your tables and columns. Your SQL involving those names will be more comprehensible for it, which is important when writing it but even more important later when trying to understand and maybe revise it.

(2) Develop **some level** of understanding of [Database normalization](https://en.wikipedia.org/wiki/Database_normalization). But do not let the complexity of the higher numbered forms dissuade you from eschewing them all. (That's why I stress "some level".)

(3) Beware of short-term-gain versus long-term-pain tradeoffs.

Good luck.

(5) By anonymous on 2020-06-16 16:25:25 in reply to 4

Thank you Larry.  I am completely new to this and a single mom. I started a small cleaning business from home after getting laid off and I thought having a small database would be helpful.  I looked at what you sent and I appreciate it, but I don't understand my error.  I did find this and thought it should work the same for me: (https://www.sqlite.org/foreignkeys.html)

  artistid    INTEGER PRIMARY KEY, 
  artistname  TEXT
  trackid     INTEGER,
  trackname   TEXT, 
  trackartist INTEGER REFERENCES artist

So in the end, not sure why it does not work.

Thank you for advice Larry

(6) By doug (doug9forester) on 2020-06-16 21:30:38 in reply to 5 [link]

Each table has a primary key which makes a unique identifier for each row in that table. The reference for a foreign key means that the value in one table refers to the row in the other table identified by the primary key. But you have to tie the two values together, as:
  artistname TEXT );
  trackname TEXT,
  trackartist INTEGER REFERENCES artist(artistid) );
If you had used the same name of the column in both tables, you could leave out the foreign key column name.

(7) By Keith Medcalf (kmedcalf) on 2020-06-16 22:05:49 in reply to 6 [link]

A REFERENCES constraint only needs to refer to the target table.  The target table (parent) columns default to the primary key of the target table.  You only need to specify the column names if they refer to something that is not the primary key of the table.  However, there must still be a unique index on those columns in the parent, just as their must be an index on the child for which the referencing columns must be the leading columns in the index.

In your example above, the REFERENCES constraint on track.trackartist does not need to contain the target column name (artistid) since that is the primary key of the referenced table.

(8) By Keith Medcalf (kmedcalf) on 2020-06-16 22:09:26 in reply to 5 [link]

Can you define what you mean by "does not work".  That is rather a vague descriptive.  Perhaps an example of what you did and the results you obtained and what you expected would help here.

Also note that the above only references the syntax for the declaration of foreign key constraints.  Actual enforcement is a separate matter which is, by default, turned off.  You need `pragma foreign_keys=on` to turn on enforcement.

(9) By Keith Medcalf (kmedcalf) on 2020-06-16 22:37:55 in reply to 1 [link]

BTW, your description is confusing.

The terminology for a relationship between two tables in a Relational Database is a Parent-Child relationship.  One table has a column(s) which is the "parent" of the relationship, and another (or the same) table contains a column(s) which is the "child".  

A "child" has exactly one "parent" (which may be NULL, as in unknown), and a "parent" may have 0 to N "child"s. 

The "REFERENCES" constraint is a constraint on the child table column(s) and points to (references) what constitutes the parent or the relationship.  

The relationship is *ALWAYS* 1 to N between parent and child -- the parent column(s) must be a unique key or primary key (candidate key).  The child column(s) should have an index on them for performance reasons.  If the child columns are constrained UNIQUE then the parent:child relationship 1:N has N limited to being 0 or 1.  If the child column(s) are NULL then that child has no parent.

(10.1) By Larry Brasfield (LarryBrasfield) on 2020-06-17 08:29:17 edited from 10.0 in reply to 5 [link]

You're welcome, of course.

Without gainsaying what Doug and Keith have said, (which I second, for what it's worth), a few more points may help it to *work*, for a definition of "work" that you may have meant and might want to adopt. (BTW, that is a word infamous for **not** conveying useful information. Better is to show what happened, and where the disfunction is not obvious from that, to show or explain what you expected or consider to *work*.)

Per that syntax diagram I linked earlier, you can specify the optional [foreign-key-clause](https://sqlite.org/syntax/foreign-key-clause.html) nested among the expansions. And when you study what it does, you will see that, without some of the optional parts below the top row of the railroad chart, your declared foreign key (with Doug's rewrite) does little more than signify intent. To get some enforcement with SQLite's help, you need <code>pragma foreign_keys=on</code> (as Keith mentioned) **and**, unless you plan to carefully honor the constraint during modifications via ordering of operations and extra code, you need to tell the DBMS what to do when, in the course of table content creation and modification, you cause the specified relationship to be violated. Absent those clauses, the enforcement you will get from SQLite is rejection, by returned error codes, of the changes that produce a violation.

(Edited to cure overstatement of bare FK clause effect.)