FK reference to other tables

[schunt1]

Currently I can only create a FK reference to tables listed above in the QuickSQL script. I would like to be able to create FK references to itself (pigs ear), tables below and tables not mentioned in the script. This would would allow me to use QuickSQL to add new tables to an existing Schema. In the examples below, only example 3 works and creates a FK. They should all work. Example 3 would need to explicitly state the reference using the /reference /fk. All used to work in previous versions of Quick SQL.

-- Generated by Quick SQL 1.2.9

Example 1 - reference below

table1 table_name description table2_id table2 table_name __description

Example 2 - reference above

table2 table_name description table1 table_name description __table2_id

Example 3 - reference other

table1 __table_name description table2_id /references table2

Example 4 - self reference

table1 __table_name description table1_id

[vadim-tropashko]

Certainly, self referencing works:

-- create tables

create table my_table (
    id              number default on null to_number(sys_guid(), 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX') 
                    constraint my_table_id_pk primary key,
    my_table_ref    number
);

-- table index
create index my_table_i1 on my_table (my_table_ref);

alter table my_table add constraint my_table_my_table_ref_fk foreign key (my_table_ref) references my_table;

-- Generated by Quick SQL 1.2.12 5/8/2024, 9:05:13 AM

/*
my_table
  my_table_ref /fk my_table

 Non-default options:
# settings = {}
*/

[vadim-tropashko]

I also don't follow your example with the _id syntax not working:

-- create tables

create table table1 (
    id             number default on null to_number(sys_guid(), 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX') 
                   constraint table1_id_pk primary key,
    table2_id      number,
    table_name     varchar2(255 char),
    description    varchar2(4000 char)
);

-- table index
create index table1_i1 on table1 (table2_id);

create table table2 (
    id            number default on null to_number(sys_guid(), 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX') 
                  constraint table2_id_pk primary key,
    table_name    varchar2(255 char),
    escription    varchar2(4000 char)
);

alter table table1 add constraint table1_table2_id_fk foreign key (table2_id) references table2;

-- Generated by Quick SQL 1.2.12 5/8/2024, 9:12:37 AM

/*
table1
  table_name
  description
  table2_id

table2
  table_name
  escription

 Non-default options:
# settings = {}

*/

Tested at https://krisrice.io/quick-sql-standalone.html

[vadim-tropashko]

The self referencing example with the _id syntax:

-- create tables

create table table1 (
    id             number default on null to_number(sys_guid(), 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX') 
                   constraint table1_id_pk primary key,
    table1_id      number,
    table_name     varchar2(255 char),
    description    varchar2(4000 char)
);

-- table index
create index table1_i1 on table1 (table1_id);

alter table table1 add constraint table1_table1_id_fk foreign key (table1_id) references table1;

-- Generated by Quick SQL 1.2.12 5/8/2024, 9:15:48 AM

/*
table1
  table_name
  description
  table1_id

 Non-default options:
# settings = {}

*/

[schunt1]

Ah yes the key constraint is created after in the alter command not inline in the table as with example 2; annoyed I missed it. Sorry and thanks for great work. Si