• Registered: 2014-09-06
  • Posts: 805

Topic: FOREIGN KEY bey script

Hi please if someone can help me to write correct the script
I like to create a new table with a FOREIGN  KEY  inside . The script I have is the following but I failed to complete it
the table name is ALFA and contains id of alfa, maera as date, phone as text , comment as text and a foreign key as id_delta

sqlexecute('CREATE TABLE Alfa (Alfa integer PRIMARY KEY, mera DATE, phone TEXT, Comment TEXT'  .. here I like to write the FOREIGN KEY.... );

  • Registered: 2014-08-02
  • Posts: 917

Re: FOREIGN KEY bey script

Hello v_pozidis
You have to use two instructions : alter (my_table) add constraint ...
alter table jobs add constraint ... foreign key (...)

Please haave a look ont this link : https://www.codegrepper.com/search.php? … eign%20key

JB

3 Reply by sparrow (edited by sparrow 2022-02-20 19:38:31)

  • From: Рідна МАТИ, UKRAINE
  • Registered: 2018-06-02
  • Posts: 908

Re: FOREIGN KEY bey script

Hi Jean.brezhonek, V_pozidis
alter table jobs add constraint ... (work in MYSQL) - does not work in SQLite  Only the RENAME TABLE, ADD COLUMN, RENAME COLUMN, and DROP COLUMN variants of the ALTER TABLE command are supported. Other kinds of ALTER TABLE operations such as ALTER COLUMN, ADD CONSTRAINT, and so forth are omitted. https://www.sqlite.org/omitted.html


Foreign is supported through table creation. https://www.sqlite.org/foreignkeys.html


Also, SQLite only supports data types: https://www.sqlite.org/datatype3.html
Storage Classes and Datatypes

Each value stored in an SQLite database (or manipulated by the database engine) has one of the following storage classes:

    NULL. The value is a NULL value.

    INTEGER. The value is a signed integer, stored in 0, 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.

    REAL. The value is a floating point value, stored as an 8-byte IEEE floating point number.

    TEXT. The value is a text string, stored using the database encoding (UTF-8, UTF-16BE or UTF-16LE).

    BLOB. The value is a blob of data, stored exactly as it was input.

SQLite does not have a storage class set aside for storing dates and/or times. Instead, the built-in Date And Time Functions of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values


Jean.brezhonek, thank you for your help.

4 Reply by v_pozidis (edited by v_pozidis 2022-02-21 08:19:21)

  • Registered: 2014-09-06
  • Posts: 805

Re: FOREIGN KEY bey script

Thanks' but I still continue to fail.. Anyway...
I have updated my database with some new columns and also  two new tables.
How can I import all my records from the old database to the new updated database?
Any idea????

  • From: Рідна МАТИ, UKRAINE
  • Registered: 2018-06-02
  • Posts: 908

Re: FOREIGN KEY bey script

Since version 5.4, MVD has a built-in ability to export data from your tables
and import data into tables without using additional scripts.
All you need is in the old version of your project to export data, for example, to .CSV format,
and in the new version (where tables and columns are added), import data from a .CSV file.
This function is located in your project's menu, unless you have disabled the built-in menu.

6 Reply by v_pozidis (edited by v_pozidis 2022-02-21 09:31:27)

  • Registered: 2014-09-06
  • Posts: 805

Re: FOREIGN KEY bey script

Hi sparrow, I know that but it doesn't  help. My database contains over 50 tables with many fields, so it is very difficult.
My idea was if I could by script insert in the old database the new columns and tables and then copy the sqlite.db in my new software.
But I can not create the new table which contains relationship. In my previous version I had add in a existing Table just one field and it works.
I read the manual but when I try it I fail.

  • From: Рідна МАТИ, UKRAINE
  • Registered: 2018-06-02
  • Posts: 908

Re: FOREIGN KEY bey script

In the last post and in this post, I gave you all the tips for the simplest solution to your problem.
What you are trying to do is theoretically possible with knowledge and experience.
In fact, all this can be done in SQLite database administration programs with knowledge and experience.
Unfortunately, teaching SQLite databases is out of the scope of this forum.

8 Reply by v_pozidis (edited by v_pozidis 2022-02-21 11:24:15)

  • Registered: 2014-09-06
  • Posts: 805

Re: FOREIGN KEY bey script

Yes you have right and thank you again for your time and your help. What I really need  to know is how to syntax correct the script.
sqlexecute('Create Table ALFA (alfa.id INTEGER PRIMARY KEY AUTOINCREMENT), id_Vita INTEGER, FOREIGN KEY (id_vita) REFERENCES vita (id_vita)');
when I place it in the software according the SQLite page it should work. but somewhere is an error and I can not find it.
Vita is an table with other fields which Alfa a relationship.

  • From: Рідна МАТИ, UKRAINE
  • Registered: 2018-06-02
  • Posts: 908

Re: FOREIGN KEY bey script

I don't know what kind of documentation you are reading if you can't write a query correctly.

Compare: 

CREATE TABLE track(
  trackid     INTEGER, 
  trackname   TEXT, 
  trackartist INTEGER,
  FOREIGN KEY(trackartist) REFERENCES artist(artistid)
);

https://sqlite.org/foreignkeys.html
Feel free to read and learn