2

I have a two Oracle tables:

CREATE TABLE MANAGEDSYSTEMS( MSYSTEMID INTEGER NOT NULL, MSYSTEMGROUPID INTEGER, SPECIALNUMBER VARCHAR2(40 ), SERIALNUMBER VARCHAR2(30 ), MSYSTEMSTATUS VARCHAR2(30 ), MSYSTEMNAME VARCHAR2(60 ), IPADDRESS VARCHAR2(30 ), DATEMSYSTEMADDED TIMESTAMP(6), DESCRIPTION CLOB ) / -- ADD KEYS FOR TABLE MANAGEDSYSTEMS ALTER TABLE MANAGEDSYSTEMS ADD CONSTRAINT MSKEY PRIMARY KEY (MSYSTEMID) / CREATE TABLE AGENTS( AGENTID INTEGER NOT NULL, MSYSTEMID INTEGER, AGENTGROUPID INTEGER, AGENTSERIALNUMBER VARCHAR2(60 ), AGENTSTATUS VARCHAR2(30 ), AGENTOS VARCHAR2(60 ), AGENTIPADDRESS VARCHAR2(40 ), LASTSYNC TIMESTAMP(6), DATEAGENTADDED TIMESTAMP(6), CPULOADLIMIT INTEGER, RAMLOADLIMIT INTEGER, HDDSPACELIMIT INTEGER, NETWORKUPLIMIT INTEGER, NETWORKDOWNLIMIT INTEGER, REPORTUSERLOGINS VARCHAR2(30 ), CANEXECCOMMANDS VARCHAR2(30 ), SYNCHRONIZATIONTIME VARCHAR2(30 ), DATALIMITSPEAKTIMES INTEGER, DESCRIPTION CLOB ) / -- ADD KEYS FOR TABLE AGENTS ALTER TABLE AGENTS ADD CONSTRAINT AGENTID PRIMARY KEY (AGENTID) ALTER TABLE AGENTS ADD CONSTRAINT MSYSTEMID FOREIGN KEY (MSYSTEMID) REFERENCES MANAGEDSYSTEMS (MSYSTEMID)

I want to assign 'Agent' into the table 'Managedsystems'. As you can see I have a table foreign key - the user must first create managed system and then to create agent. But I also want to give to give a option to create agents without assigning a managed system. When I tried to change the key of the managed system id into the agents table I get this error:

Caused by: java.sql.SQLIntegrityConstraintViolationException: ORA-02291: integrity constraint (ADMIN.MSYSTEMID) violated - parent key not found

It seems that I cannot assign empty values. How I can fix this problem?

UPDATE:

I use this SQL statement to update the agent table:

String sql = "UPDATE AGENTS " + " SET AGENTID = ?, MSYSTEMID = ?, AGENTGROUPID = ?, AGENTSERIALNUMBER = ?, AGENTSTATUS = ?, AGENTOS = ?, AGENTIPADDRESS = ?, " + " LASTSYNC = to_timestamp(?, " + ts_template + "), DATEAGENTADDED = to_timestamp(?, " + ts_template + "), " + " CPULOADLIMIT = ?, RAMLOADLIMIT = ?, HDDSPACELIMIT = ?, NETWORKUPLIMIT = ?, NETWORKDOWNLIMIT = ?, REPORTUSERLOGINS = ?, " + " CANEXECCOMMANDS = ?, SYNCHRONIZATIONTIME = ?, DATALIMITSPEAKTIMES = ?, " + " DESCRIPTION = ? WHERE AGENTID = ?";
Improve this question
8
  • why don’t put the primary key directly in the CREATE statement? Commented Dec 11, 2012 at 11:20
  • This sql script is created with Toad Database modeler Commented Dec 11, 2012 at 11:22
  • @skp: that wouldn't help for this problem. Commented Dec 11, 2012 at 11:23
  • @JSFUser: please show us the SQL statement that caused the error. Commented Dec 11, 2012 at 11:23
  • if you mean you want to add an agent without a "MANAGEDSYSTEM" even by the time you commit, then leave the MSYSTEMID blank. if you mean you want to add agents and then add a managed system (1 transaction), then alter the FK to be initially deferred. Commented Dec 11, 2012 at 11:27

2 Answers 2

Reset to default
1

What value you bind in your update statement? AGENTS.MSYSTEMID's value should be any of value which exists in parent table(MANAGEDSYSTEMS) or null if you don't want to assign. So the code should like this:

String sql = "..."; .. PreparedStatement ps = ... ps.setInt(1, ...); if (you want to assign AGENTS.MSYSTEMID) { ps.setInt(2, ...); } else { ps.setNull(2, ...); } ...
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

if you're inserting into both tables in the same transaction, but you want to insert into the child table first then make your FK initially deferred.

SQL> CREATE TABLE MANAGEDSYSTEMS( 2 MSYSTEMID INTEGER NOT NULL, 3 MSYSTEMGROUPID INTEGER, 4 SPECIALNUMBER VARCHAR2(40 ), 5 SERIALNUMBER VARCHAR2(30 ), 6 MSYSTEMSTATUS VARCHAR2(30 ), 7 MSYSTEMNAME VARCHAR2(60 ), 8 IPADDRESS VARCHAR2(30 ), 9 DATEMSYSTEMADDED TIMESTAMP(6), 10 DESCRIPTION CLOB 11 ) 12 / Table created. SQL> ALTER TABLE MANAGEDSYSTEMS ADD CONSTRAINT MSKEY PRIMARY KEY (MSYSTEMID); Table altered. SQL> CREATE TABLE AGENTS( 2 AGENTID INTEGER NOT NULL, 3 MSYSTEMID INTEGER, 4 AGENTGROUPID INTEGER, 5 AGENTSERIALNUMBER VARCHAR2(60 ), 6 AGENTSTATUS VARCHAR2(30 ), 7 AGENTOS VARCHAR2(60 ), 8 AGENTIPADDRESS VARCHAR2(40 ), 9 LASTSYNC TIMESTAMP(6), 10 DATEAGENTADDED TIMESTAMP(6), 11 CPULOADLIMIT INTEGER, 12 RAMLOADLIMIT INTEGER, 13 HDDSPACELIMIT INTEGER, 14 NETWORKUPLIMIT INTEGER, 15 NETWORKDOWNLIMIT INTEGER, 16 REPORTUSERLOGINS VARCHAR2(30 ), 17 CANEXECCOMMANDS VARCHAR2(30 ), 18 SYNCHRONIZATIONTIME VARCHAR2(30 ), 19 DATALIMITSPEAKTIMES INTEGER, 20 DESCRIPTION CLOB 21 ) 22 / Table created. SQL> ALTER TABLE AGENTS ADD CONSTRAINT AGENTID PRIMARY KEY (AGENTID); Table altered. SQL> ALTER TABLE AGENTS ADD CONSTRAINT MSYSTEMID FOREIGN KEY (MSYSTEMID) REFERENCES MANAGEDSYSTEMS (MSYSTEMID) initially deferred; Table altered. SQL> insert into AGENTS (AGENTID, MSYSTEMID, AGENTGROUPID, AGENTSERIALNUMBER) values (1, 1, 1, 'x'); 1 row created. SQL> insert into MANAGEDSYSTEMS (MSYSTEMID, MSYSTEMGROUPID, SPECIALNUMBER) values (1, 1, 'x'); 1 row created. SQL> commit; Commit complete.

it wont let you finialise the transaction without the parent though:

SQL> insert into AGENTS (AGENTID, MSYSTEMID, AGENTGROUPID, AGENTSERIALNUMBER) values (2, 2, 1, 'x'); 1 row created. SQL> commit; commit * ERROR at line 1: ORA-02091: transaction rolled back ORA-02291: integrity constraint (DTD_TRADE.MSYSTEMID) violated - parent key not found
Improve this answer

2 Comments

I have one problem. I have defined the variable which holds the msystemid as Integer. I cannot insert null into the input filed. But I can insert 0.
@JSFUser integers will accept null too (DB side). just remove the MSYSTEMID = ?, from your sql command client side if it wont accept passing a null.