Re: Self-referential foreign key
This may help: https://stackoverflow.com/questions/29297043/on-delete-set-null-on-self-referencing-relationship On 3/23/21 6:09 AM, John English wrote: I have a table in which I want to include a self-referential foreign key to the same table: CREATE TABLE x ( id INTEGER GENERATED ALWAYS AS IDENTITY, idY INTEGER, idX INTEGER DEFAULT NULL, CONSTRAINT x_pk PRIMARY KEY (id), CONSTRAINT x_1 FOREIGN KEY (idY) REFERENCES y(id) ON DELETE CASCADE, CONSTRAINT x_2 FOREIGN KEY (idX) REFERENCES x(id) ON DELETE SET NULL ); When I try this I get the following error: Error: Foreign Key 'X_2' is invalid because 'The delete rule of foreign key must be CASCADE. (The referential constraint is self-referencing and the table is dependent in a relationship with a delete rule of CASCADE.)'. SQLState: 42915 ErrorCode: 3 My intention is that when rows from table Y are deleted, corresponding rows from X are also deleted, but when rows from X are deleted, the references in any referencing rows in X are just set to NULL. I don't understand exactly what I'm doing wrong here. How can I rewrite the table definition to do what I want? Thanks,
Re: Embedded database, authentication, and derby.system.home
The ij CONNECT command issues a DriverManager.getConnection(jdbcURL)
command. What does your connection logic look like?
Thanks,
-Rick
On 3/23/21 4:59 AM, Michael Remijan wrote:
I'll see if I can reproduce it with ij, but I'm not sure. The error is with the
Java client connecting to and starting an embedded database. There's no SQL at
play since it's the connecting and starting that's failing. If I can reproduce
it with ij I'll let you know.
-Original Message-
From: Rick Hillegas
Sent: Monday, March 22, 2021 1:09 PM
To: derby-user@db.apache.org
Subject: Re: Embedded database, authentication, and derby.system.home
Sorry. I need SQL scripts. I can't make sense of a compiled application
with no source code.
On 3/22/21 7:05 AM, Michael Remijan wrote:
Hi Rick,
Here is a working Java example for you. It's fully self-contained with its own JRE so all
you need do is download it and run it. This bin distribution is for Windows, so sorry if
you're on another platform. It's too big for email, so I have it on my OneDrive
https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2F1drv.ms%2Fu%2Fs!Ao0GHBDYRFEMgbMss-5a_R3q1GOvBQ%3Fe%3DnpfMBddata=04%7C01%7C%7Cc6fb22f26340407b616708d8ed5d8d2e%7C84df9e7fe9f640afb435%7C1%7C0%7C63752038352293%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000sdata=jBxtsRB9m9TvwKtQyrIrTFjajbrz%2FUTDqXbECfFuhD4%3Dreserved=0
First, download, unzip, then run /bin/start-resiste.bat. You'll see the
following:
[cid:image001.png@01D71EF8.C4AFB090]
Next, m move the /data directory to a different location and then update
/conf/db.properties.
[cid:image002.png@01D71EF9.233513D0]
Next, run /bin/start-resiste.bat again. You'll see the following error:
[cid:image003.png@01D71EF9.233513D0]
Next, update /bin/start-resiste.bat to make sure derby.system.home is in sync
with the new location of the database:
[cid:image004.png@01D71EF9.3A7B5450]
Next, run /bin/start-resiste.bat again. You'll see it's now successful with
/conf/db.properties and /bin/start-resiste.bat in sync
[cid:image001.png@01D71EF8.C4AFB090]
Now here is a big Caveat. I discovered this while getting this demo together. I
have Derby 10.14.2.0 installed on my computer. For my first attempt at this
demo, I created the database from scratch using 10.14.2.0. When I did this, I
DID NOT experience the connection error as described above. However, when I use
my original database, I DO experience the error as demonstrated above.
Unfortunately, I do not remember which version of Derby I used to create the
original database, but obviously it was an older version. So perhaps this has
something to do with an older database version?
Mike
From: Rick Hillegas
Sent: Friday, March 19, 2021 4:04 PM
To: derby-user@db.apache.org
Subject: Re: Embedded database, authentication, and derby.system.home
Hi Michael,
I must be missing something in your description of the problem, because I
cannot reproduce it. I have created the following scripts in order to reproduce
the behavior of both changing the value of derby.system.home and moving the
database to another directory:
First SQL script nativeTest.sql:
CONNECT 'jdbc:derby:/Users/rhillegas/derby/mainline/z/db1;create=true;user=dbo';
CALL syscs_util.syscs_create_user( 'DBO', 'dbo_password' );
-- shutdown in order to enable NATIVE authentication
CONNECT 'jdbc:derby:/Users/rhillegas/derby/mainline/z/db1;shutdown=true';
CONNECT
'jdbc:derby:/Users/rhillegas/derby/mainline/z/db1;user=dbo;password=dbo_password';
VALUES SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('derby.authentication.provider');
CREATE TABLE t(a INT);
INSERT INTO t VALUES (1), (2);
Second SQL script nativeTest2.sql:
CONNECT
'jdbc:derby:/Users/rhillegas/derby/mainline/zz/db1;user=dbo;password=dbo_password';
SELECT * FROM t;
Then a shell script (z1) to run nativeTest.sql, move the database to another
directory, then run nativeTest2.sql:
#! /bin/bash
#
# Run a derby test
rm -rf z/db1
rm -rf zz/db1
rm zzz/*
rm /*
. setupClasspath
java -cp $CLASSPATH -Dderby.system.home=/Users/rhillegas/derby/mainline/zzz
-Dderby.language.logStatementText=true -Dderby.stream.error.logSeverityLevel=0
org.apache.derby.tools.ij $MAC_HOME/sql/nativeTest.sql
mv z/db1 zz/
java -cp $CLASSPATH -Dderby.system.home=/Users/rhillegas/derby/mainline/
-Dderby.language.logStatementText=true -Dderby.stream.error.logSeverityLevel=0
org.apache.derby.tools.ij $MAC_HOME/sql/nativeTest2.sql
When I run z1, I get the following output:
ij version 10.16
ij> CONNECT
'jdbc:derby:/Users/rhillegas/derby/mainline/z/db1;create=true;user=dbo';
ij> CALL syscs_util.syscs_create_user( 'DBO', 'dbo_password' );
0 rows inserted/updated/deleted
ij> -- shutdown in order to enable NATIVE authentication
CONNECT 'jdbc:derby:/Users/rhillegas/derby/mainline/z/db1;shutdown=true';
ERROR 08006: Database
Self-referential foreign key
I have a table in which I want to include a self-referential foreign key to the same table: CREATE TABLE x ( id INTEGER GENERATED ALWAYS AS IDENTITY, idY INTEGER, idX INTEGER DEFAULT NULL, CONSTRAINT x_pk PRIMARY KEY (id), CONSTRAINT x_1FOREIGN KEY (idY) REFERENCES y(id) ON DELETE CASCADE, CONSTRAINT x_2FOREIGN KEY (idX) REFERENCES x(id) ON DELETE SET NULL ); When I try this I get the following error: Error: Foreign Key 'X_2' is invalid because 'The delete rule of foreign key must be CASCADE. (The referential constraint is self-referencing and the table is dependent in a relationship with a delete rule of CASCADE.)'. SQLState: 42915 ErrorCode: 3 My intention is that when rows from table Y are deleted, corresponding rows from X are also deleted, but when rows from X are deleted, the references in any referencing rows in X are just set to NULL. I don't understand exactly what I'm doing wrong here. How can I rewrite the table definition to do what I want? Thanks, -- John English
RE: Embedded database, authentication, and derby.system.home
I'll see if I can reproduce it with ij, but I'm not sure. The error is with the
Java client connecting to and starting an embedded database. There's no SQL at
play since it's the connecting and starting that's failing. If I can reproduce
it with ij I'll let you know.
-Original Message-
From: Rick Hillegas
Sent: Monday, March 22, 2021 1:09 PM
To: derby-user@db.apache.org
Subject: Re: Embedded database, authentication, and derby.system.home
Sorry. I need SQL scripts. I can't make sense of a compiled application
with no source code.
On 3/22/21 7:05 AM, Michael Remijan wrote:
> Hi Rick,
>
> Here is a working Java example for you. It's fully self-contained with its
> own JRE so all you need do is download it and run it. This bin distribution
> is for Windows, so sorry if you're on another platform. It's too big for
> email, so I have it on my OneDrive
> https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2F1drv.ms%2Fu%2Fs!Ao0GHBDYRFEMgbMss-5a_R3q1GOvBQ%3Fe%3DnpfMBddata=04%7C01%7C%7Cc6fb22f26340407b616708d8ed5d8d2e%7C84df9e7fe9f640afb435%7C1%7C0%7C63752038352293%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000sdata=jBxtsRB9m9TvwKtQyrIrTFjajbrz%2FUTDqXbECfFuhD4%3Dreserved=0
>
> First, download, unzip, then run /bin/start-resiste.bat. You'll see the
> following:
> [cid:image001.png@01D71EF8.C4AFB090]
>
>
> Next, m move the /data directory to a different location and then update
> /conf/db.properties.
> [cid:image002.png@01D71EF9.233513D0]
>
>
> Next, run /bin/start-resiste.bat again. You'll see the following error:
> [cid:image003.png@01D71EF9.233513D0]
>
>
> Next, update /bin/start-resiste.bat to make sure derby.system.home is in sync
> with the new location of the database:
> [cid:image004.png@01D71EF9.3A7B5450]
>
> Next, run /bin/start-resiste.bat again. You'll see it's now successful with
> /conf/db.properties and /bin/start-resiste.bat in sync
> [cid:image001.png@01D71EF8.C4AFB090]
>
>
> Now here is a big Caveat. I discovered this while getting this demo together.
> I have Derby 10.14.2.0 installed on my computer. For my first attempt at this
> demo, I created the database from scratch using 10.14.2.0. When I did this, I
> DID NOT experience the connection error as described above. However, when I
> use my original database, I DO experience the error as demonstrated above.
> Unfortunately, I do not remember which version of Derby I used to create the
> original database, but obviously it was an older version. So perhaps this has
> something to do with an older database version?
>
> Mike
>
>
>
>
>
> From: Rick Hillegas
> Sent: Friday, March 19, 2021 4:04 PM
> To: derby-user@db.apache.org
> Subject: Re: Embedded database, authentication, and derby.system.home
>
> Hi Michael,
>
> I must be missing something in your description of the problem, because I
> cannot reproduce it. I have created the following scripts in order to
> reproduce the behavior of both changing the value of derby.system.home and
> moving the database to another directory:
>
> First SQL script nativeTest.sql:
>
> CONNECT
> 'jdbc:derby:/Users/rhillegas/derby/mainline/z/db1;create=true;user=dbo';
>
>
>
> CALL syscs_util.syscs_create_user( 'DBO', 'dbo_password' );
>
>
>
> -- shutdown in order to enable NATIVE authentication
>
> CONNECT 'jdbc:derby:/Users/rhillegas/derby/mainline/z/db1;shutdown=true';
>
>
>
> CONNECT
> 'jdbc:derby:/Users/rhillegas/derby/mainline/z/db1;user=dbo;password=dbo_password';
>
>
>
> VALUES
> SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('derby.authentication.provider');
>
>
>
> CREATE TABLE t(a INT);
>
> INSERT INTO t VALUES (1), (2);
>
>
> Second SQL script nativeTest2.sql:
>
> CONNECT
> 'jdbc:derby:/Users/rhillegas/derby/mainline/zz/db1;user=dbo;password=dbo_password';
>
>
>
> SELECT * FROM t;
>
>
> Then a shell script (z1) to run nativeTest.sql, move the database to another
> directory, then run nativeTest2.sql:
>
> #! /bin/bash
>
> #
>
> # Run a derby test
>
>
>
> rm -rf z/db1
>
> rm -rf zz/db1
>
> rm zzz/*
>
> rm /*
>
>
>
> . setupClasspath
>
>
>
> java -cp $CLASSPATH -Dderby.system.home=/Users/rhillegas/derby/mainline/zzz
> -Dderby.language.logStatementText=true
> -Dderby.stream.error.logSeverityLevel=0 org.apache.derby.tools.ij
> $MAC_HOME/sql/nativeTest.sql
>
>
>
> mv z/db1 zz/
>
>
>
> java -cp $CLASSPATH -Dderby.system.home=/Users/rhillegas/derby/mainline/
> -Dderby.language.logStatementText=true
> -Dderby.stream.error.logSeverityLevel=0 org.apache.derby.tools.ij
> $MAC_HOME/sql/nativeTest2.sql
>
>
> When I run z1, I get the following output:
>
> ij version 10.16
>
> ij> CONNECT
> 'jdbc:derby:/Users/rhillegas/derby/mainline/z/db1;create=true;user=dbo';
>
> ij> CALL syscs_util.syscs_create_user( 'DBO', 'dbo_password' );
>
> 0 rows inserted/updated/deleted
>
> ij> -- shutdown in order to enable NATIVE authentication
>
> CONNECT
