 | |
Help with Oracle constrant on two tables problem
comp.databases.oracle.misc comp.databases.oracle.misc
 |
| | Thread Tools | Display Modes |
#31
 
| |||
| |||
|
Re: Help with Oracle constrant on two tables problem -
02-19-2008
, 04:05 AM
|
Hello David. When I type ALTER TABLE tblT2 ADD CONSTRAINT DI_CorrectMethodSetCount CHECK exists (SELECT tblt2.id,tblt1.id1 FROM tblT1, tblT2 WHERE tblT1.id1 = tblT2.id1 AND tblT1.id1 = tblt2.id1 GROUP BY tblT1.checkCount, tblt1.id1, tblt2.id HAVING COUNT(*) = tblT1.checkCount-1); my error is ORA-00906: missing left parenthesis |
robert
#32
| |||
| |||
|
|
Hello David. When I type ALTER TABLE tblT2 ADD CONSTRAINT DI_CorrectMethodSetCount CHECK exists (SELECT tblt2.id,tblt1.id1 FROM tblT1, tblT2 WHERE tblT1.id1 = tblT2.id1 AND tblT1.id1 = tblt2.id1 GROUP BY tblT1.checkCount, tblt1.id1, tblt2.id HAVING COUNT(*) = tblT1.checkCount-1); my error is ORA-00906: missing left parenthesis |
robert
#33
| |||
| |||
|
|
Hello David. When I type ALTER TABLE tblT2 ADD CONSTRAINT DI_CorrectMethodSetCount CHECK exists (SELECT tblt2.id,tblt1.id1 FROM tblT1, tblT2 WHERE tblT1.id1 = tblT2.id1 AND tblT1.id1 = tblt2.id1 GROUP BY tblT1.checkCount, tblt1.id1, tblt2.id HAVING COUNT(*) = tblT1.checkCount-1); my error is ORA-00906: missing left parenthesis |
robert
#34
| |||
| |||
|
|
On 18.02.2008 09:42, Michael O'Shea wrote: On Feb 16, 2:17 pm, jharbo... (AT) googlemail (DOT) com wrote: This is my first post to Google groups. I am write with a new program I am writing using Oracle XE on Windows XP home. Here it is. I have a table called "table1" that has the following in it id1 * * checkCount *1 * * * * 4 *2 * * * * 5 *3 * * * * 10 *6 * * * * 14 *9 * * * * 5 "table2" has the following id1 * * *Description *1 * * * id1description1 *1 * * * id1description2 *1 * * * id1description3 *1 * * * id1description4 *2 * * * id2description1 *2 * * * id2description2 *2 * * * id2description3 *2 * * * id2description4 *2 * * * id2description5 checkCount is the number of rows in "table2" that must have the same id1 (there are 4 rows with id1's with value 1, there are 5 rows with id1's with value 2 as in "table1") I need to guarentee applications don't write to "table2" with an incorrect number of rows (for example to write 1, 2, 3, 5, 6, 7, 8, etc number of rows with id1. it should have 4 rows and always 4). Can this be done with oracle XE with constrants? Using your dataset I have demonstrated a FK NN retrofit approach for you below. I have assumed you omitted to state that the lookup table defining the number of rows that should exist in the parent table grouped by id may be referenced more than once. SQL> CREATE TABLE tblT1(id1 NUMBER, checkCount NUMBER); Table created. SQL> INSERT INTO tblT1(id1,checkCount) * 2 * VALUES(1,4); 1 row created. SQL> INSERT INTO tblT1(id1,checkCount) * 2 * VALUES(2,5); 1 row created. SQL> INSERT INTO tblT1(id1,checkCount) * 2 * VALUES(3,10); 1 row created. SQL> INSERT INTO tblT1(id1,checkCount) * 2 * VALUES(6,14); 1 row created. SQL> INSERT INTO tblT1(id1,checkCount) * 2 * VALUES(9,5); 1 row created. SQL SQL SQL> CREATE TABLE tblT2(id NUMBER, id1 NUMBER, description VARCHAR2(15)); Table created. SQL> CREATE TABLE tblValid(id NUMBER,id1 NUMBER, PRIMARY KEY(id,id1)) ORGANIZATION INDEX; Table created. SQL> ALTER TABLE tblT2 * 2 * ADD CONSTRAINT DI_CorrectMethodSetCount * 3 * *FOREIGN KEY(id,id1) * 4 * * REFERENCES tblValid(id,id1) * 5 * * INITIALLY DEFERRED DEFERRABLE; Table altered. SQL> CREATE OR REPLACE TRIGGER trgT2 * 2 * BEFORE INSERT ON tblT2 * 3 * *FOR EACH ROW * 4 *DECLARE * 5 *BEGIN * 6 * DELETE FROM tblValid * 7 * *WHERE id1 = :NEW.id1 * 8 * * *AND id = :NEW.id; * 9 *10 * INSERT INTO tblValid(id,id1) *11 * *SELECT tblt2.id,tblt1.id1 *12 * * FROM tblT1, tblT2 *13 * * *WHERE tblT1.id1 = tblT2.id1 *14 * * * AND tblT1.id1 = :NEW.id1 *15 * * * GROUP BY tblT1.checkCount, tblt1.id1, tblt2.id *16 * * * *HAVING COUNT(*) = tblT1.checkCount-1; *17 *END; *18 */ Trigger created. One question and one remark: is there a particular reason that you choose DELETE and INSERT over UPDATE and INSERT in your trigger? As far as I can see your code does not prevent errors introduced through UPDATE and DELETE. Kind regards * * * * robert- Hide quoted text - - Show quoted text - |
Robert, Hi. Comments in-line.
Quote:
|
One question and one remark: is there a particular reason that you choose DELETE and INSERT over UPDATE and INSERT in your trigger? |
"existence" requires a delete first so the transaction fails if the
insert inserts no rows (ie. if no rows are returned by the select
because of the "HAVING COUNT(*) = tblT1.checkCount-1").
Quote:
|
As far as I can see your code does not prevent errors introduced through UPDATE and DELETE. |
have the trigger fire on an insert/update. Thanks for pointing this
out.
Regards
Mike
TESSELLA Michael.OShea (AT) tessella (DOT) com
__/__/__/ Tessella Support Services plc
__/__/__/ 3 Vineyard Chambers, ABINGDON, OX14 3PX, England
__/__/__/ Tel: (44)(0)1235-555511 Fax: (44)(0)1235-553301
www.tessella.com Registered in England No. 1466429
#35
| |||
| |||
|
|
On 18.02.2008 09:42, Michael O'Shea wrote: On Feb 16, 2:17 pm, jharbo... (AT) googlemail (DOT) com wrote: This is my first post to Google groups. I am write with a new program I am writing using Oracle XE on Windows XP home. Here it is. I have a table called "table1" that has the following in it id1 * * checkCount *1 * * * * 4 *2 * * * * 5 *3 * * * * 10 *6 * * * * 14 *9 * * * * 5 "table2" has the following id1 * * *Description *1 * * * id1description1 *1 * * * id1description2 *1 * * * id1description3 *1 * * * id1description4 *2 * * * id2description1 *2 * * * id2description2 *2 * * * id2description3 *2 * * * id2description4 *2 * * * id2description5 checkCount is the number of rows in "table2" that must have the same id1 (there are 4 rows with id1's with value 1, there are 5 rows with id1's with value 2 as in "table1") I need to guarentee applications don't write to "table2" with an incorrect number of rows (for example to write 1, 2, 3, 5, 6, 7, 8, etc number of rows with id1. it should have 4 rows and always 4). Can this be done with oracle XE with constrants? Using your dataset I have demonstrated a FK NN retrofit approach for you below. I have assumed you omitted to state that the lookup table defining the number of rows that should exist in the parent table grouped by id may be referenced more than once. SQL> CREATE TABLE tblT1(id1 NUMBER, checkCount NUMBER); Table created. SQL> INSERT INTO tblT1(id1,checkCount) * 2 * VALUES(1,4); 1 row created. SQL> INSERT INTO tblT1(id1,checkCount) * 2 * VALUES(2,5); 1 row created. SQL> INSERT INTO tblT1(id1,checkCount) * 2 * VALUES(3,10); 1 row created. SQL> INSERT INTO tblT1(id1,checkCount) * 2 * VALUES(6,14); 1 row created. SQL> INSERT INTO tblT1(id1,checkCount) * 2 * VALUES(9,5); 1 row created. SQL SQL SQL> CREATE TABLE tblT2(id NUMBER, id1 NUMBER, description VARCHAR2(15)); Table created. SQL> CREATE TABLE tblValid(id NUMBER,id1 NUMBER, PRIMARY KEY(id,id1)) ORGANIZATION INDEX; Table created. SQL> ALTER TABLE tblT2 * 2 * ADD CONSTRAINT DI_CorrectMethodSetCount * 3 * *FOREIGN KEY(id,id1) * 4 * * REFERENCES tblValid(id,id1) * 5 * * INITIALLY DEFERRED DEFERRABLE; Table altered. SQL> CREATE OR REPLACE TRIGGER trgT2 * 2 * BEFORE INSERT ON tblT2 * 3 * *FOR EACH ROW * 4 *DECLARE * 5 *BEGIN * 6 * DELETE FROM tblValid * 7 * *WHERE id1 = :NEW.id1 * 8 * * *AND id = :NEW.id; * 9 *10 * INSERT INTO tblValid(id,id1) *11 * *SELECT tblt2.id,tblt1.id1 *12 * * FROM tblT1, tblT2 *13 * * *WHERE tblT1.id1 = tblT2.id1 *14 * * * AND tblT1.id1 = :NEW.id1 *15 * * * GROUP BY tblT1.checkCount, tblt1.id1, tblt2.id *16 * * * *HAVING COUNT(*) = tblT1.checkCount-1; *17 *END; *18 */ Trigger created. One question and one remark: is there a particular reason that you choose DELETE and INSERT over UPDATE and INSERT in your trigger? As far as I can see your code does not prevent errors introduced through UPDATE and DELETE. Kind regards * * * * robert- Hide quoted text - - Show quoted text - |
Robert, Hi. Comments in-line.
Quote:
|
One question and one remark: is there a particular reason that you choose DELETE and INSERT over UPDATE and INSERT in your trigger? |
"existence" requires a delete first so the transaction fails if the
insert inserts no rows (ie. if no rows are returned by the select
because of the "HAVING COUNT(*) = tblT1.checkCount-1").
Quote:
|
As far as I can see your code does not prevent errors introduced through UPDATE and DELETE. |
have the trigger fire on an insert/update. Thanks for pointing this
out.
Regards
Mike
TESSELLA Michael.OShea (AT) tessella (DOT) com
__/__/__/ Tessella Support Services plc
__/__/__/ 3 Vineyard Chambers, ABINGDON, OX14 3PX, England
__/__/__/ Tel: (44)(0)1235-555511 Fax: (44)(0)1235-553301
www.tessella.com Registered in England No. 1466429
#36
| |||
| |||
|
|
On 18.02.2008 09:42, Michael O'Shea wrote: On Feb 16, 2:17 pm, jharbo... (AT) googlemail (DOT) com wrote: This is my first post to Google groups. I am write with a new program I am writing using Oracle XE on Windows XP home. Here it is. I have a table called "table1" that has the following in it id1 * * checkCount *1 * * * * 4 *2 * * * * 5 *3 * * * * 10 *6 * * * * 14 *9 * * * * 5 "table2" has the following id1 * * *Description *1 * * * id1description1 *1 * * * id1description2 *1 * * * id1description3 *1 * * * id1description4 *2 * * * id2description1 *2 * * * id2description2 *2 * * * id2description3 *2 * * * id2description4 *2 * * * id2description5 checkCount is the number of rows in "table2" that must have the same id1 (there are 4 rows with id1's with value 1, there are 5 rows with id1's with value 2 as in "table1") I need to guarentee applications don't write to "table2" with an incorrect number of rows (for example to write 1, 2, 3, 5, 6, 7, 8, etc number of rows with id1. it should have 4 rows and always 4). Can this be done with oracle XE with constrants? Using your dataset I have demonstrated a FK NN retrofit approach for you below. I have assumed you omitted to state that the lookup table defining the number of rows that should exist in the parent table grouped by id may be referenced more than once. SQL> CREATE TABLE tblT1(id1 NUMBER, checkCount NUMBER); Table created. SQL> INSERT INTO tblT1(id1,checkCount) * 2 * VALUES(1,4); 1 row created. SQL> INSERT INTO tblT1(id1,checkCount) * 2 * VALUES(2,5); 1 row created. SQL> INSERT INTO tblT1(id1,checkCount) * 2 * VALUES(3,10); 1 row created. SQL> INSERT INTO tblT1(id1,checkCount) * 2 * VALUES(6,14); 1 row created. SQL> INSERT INTO tblT1(id1,checkCount) * 2 * VALUES(9,5); 1 row created. SQL SQL SQL> CREATE TABLE tblT2(id NUMBER, id1 NUMBER, description VARCHAR2(15)); Table created. SQL> CREATE TABLE tblValid(id NUMBER,id1 NUMBER, PRIMARY KEY(id,id1)) ORGANIZATION INDEX; Table created. SQL> ALTER TABLE tblT2 * 2 * ADD CONSTRAINT DI_CorrectMethodSetCount * 3 * *FOREIGN KEY(id,id1) * 4 * * REFERENCES tblValid(id,id1) * 5 * * INITIALLY DEFERRED DEFERRABLE; Table altered. SQL> CREATE OR REPLACE TRIGGER trgT2 * 2 * BEFORE INSERT ON tblT2 * 3 * *FOR EACH ROW * 4 *DECLARE * 5 *BEGIN * 6 * DELETE FROM tblValid * 7 * *WHERE id1 = :NEW.id1 * 8 * * *AND id = :NEW.id; * 9 *10 * INSERT INTO tblValid(id,id1) *11 * *SELECT tblt2.id,tblt1.id1 *12 * * FROM tblT1, tblT2 *13 * * *WHERE tblT1.id1 = tblT2.id1 *14 * * * AND tblT1.id1 = :NEW.id1 *15 * * * GROUP BY tblT1.checkCount, tblt1.id1, tblt2.id *16 * * * *HAVING COUNT(*) = tblT1.checkCount-1; *17 *END; *18 */ Trigger created. One question and one remark: is there a particular reason that you choose DELETE and INSERT over UPDATE and INSERT in your trigger? As far as I can see your code does not prevent errors introduced through UPDATE and DELETE. Kind regards * * * * robert- Hide quoted text - - Show quoted text - |
Robert, Hi. Comments in-line.
Quote:
|
One question and one remark: is there a particular reason that you choose DELETE and INSERT over UPDATE and INSERT in your trigger? |
"existence" requires a delete first so the transaction fails if the
insert inserts no rows (ie. if no rows are returned by the select
because of the "HAVING COUNT(*) = tblT1.checkCount-1").
Quote:
|
As far as I can see your code does not prevent errors introduced through UPDATE and DELETE. |
have the trigger fire on an insert/update. Thanks for pointing this
out.
Regards
Mike
TESSELLA Michael.OShea (AT) tessella (DOT) com
__/__/__/ Tessella Support Services plc
__/__/__/ 3 Vineyard Chambers, ABINGDON, OX14 3PX, England
__/__/__/ Tel: (44)(0)1235-555511 Fax: (44)(0)1235-553301
www.tessella.com Registered in England No. 1466429
#37
| |||
| |||
|
|
On 18.02.2008 09:42, Michael O'Shea wrote: On Feb 16, 2:17 pm, jharbo... (AT) googlemail (DOT) com wrote: This is my first post to Google groups. I am write with a new program I am writing using Oracle XE on Windows XP home. Here it is. I have a table called "table1" that has the following in it id1 * * checkCount *1 * * * * 4 *2 * * * * 5 *3 * * * * 10 *6 * * * * 14 *9 * * * * 5 "table2" has the following id1 * * *Description *1 * * * id1description1 *1 * * * id1description2 *1 * * * id1description3 *1 * * * id1description4 *2 * * * id2description1 *2 * * * id2description2 *2 * * * id2description3 *2 * * * id2description4 *2 * * * id2description5 checkCount is the number of rows in "table2" that must have the same id1 (there are 4 rows with id1's with value 1, there are 5 rows with id1's with value 2 as in "table1") I need to guarentee applications don't write to "table2" with an incorrect number of rows (for example to write 1, 2, 3, 5, 6, 7, 8, etc number of rows with id1. it should have 4 rows and always 4). Can this be done with oracle XE with constrants? Using your dataset I have demonstrated a FK NN retrofit approach for you below. I have assumed you omitted to state that the lookup table defining the number of rows that should exist in the parent table grouped by id may be referenced more than once. SQL> CREATE TABLE tblT1(id1 NUMBER, checkCount NUMBER); Table created. SQL> INSERT INTO tblT1(id1,checkCount) * 2 * VALUES(1,4); 1 row created. SQL> INSERT INTO tblT1(id1,checkCount) * 2 * VALUES(2,5); 1 row created. SQL> INSERT INTO tblT1(id1,checkCount) * 2 * VALUES(3,10); 1 row created. SQL> INSERT INTO tblT1(id1,checkCount) * 2 * VALUES(6,14); 1 row created. SQL> INSERT INTO tblT1(id1,checkCount) * 2 * VALUES(9,5); 1 row created. SQL SQL SQL> CREATE TABLE tblT2(id NUMBER, id1 NUMBER, description VARCHAR2(15)); Table created. SQL> CREATE TABLE tblValid(id NUMBER,id1 NUMBER, PRIMARY KEY(id,id1)) ORGANIZATION INDEX; Table created. SQL> ALTER TABLE tblT2 * 2 * ADD CONSTRAINT DI_CorrectMethodSetCount * 3 * *FOREIGN KEY(id,id1) * 4 * * REFERENCES tblValid(id,id1) * 5 * * INITIALLY DEFERRED DEFERRABLE; Table altered. SQL> CREATE OR REPLACE TRIGGER trgT2 * 2 * BEFORE INSERT ON tblT2 * 3 * *FOR EACH ROW * 4 *DECLARE * 5 *BEGIN * 6 * DELETE FROM tblValid * 7 * *WHERE id1 = :NEW.id1 * 8 * * *AND id = :NEW.id; * 9 *10 * INSERT INTO tblValid(id,id1) *11 * *SELECT tblt2.id,tblt1.id1 *12 * * FROM tblT1, tblT2 *13 * * *WHERE tblT1.id1 = tblT2.id1 *14 * * * AND tblT1.id1 = :NEW.id1 *15 * * * GROUP BY tblT1.checkCount, tblt1.id1, tblt2.id *16 * * * *HAVING COUNT(*) = tblT1.checkCount-1; *17 *END; *18 */ Trigger created. One question and one remark: is there a particular reason that you choose DELETE and INSERT over UPDATE and INSERT in your trigger? As far as I can see your code does not prevent errors introduced through UPDATE and DELETE. Kind regards * * * * robert- Hide quoted text - - Show quoted text - |
Robert, Hi. Comments in-line.
Quote:
|
One question and one remark: is there a particular reason that you choose DELETE and INSERT over UPDATE and INSERT in your trigger? |
"existence" requires a delete first so the transaction fails if the
insert inserts no rows (ie. if no rows are returned by the select
because of the "HAVING COUNT(*) = tblT1.checkCount-1").
Quote:
|
As far as I can see your code does not prevent errors introduced through UPDATE and DELETE. |
have the trigger fire on an insert/update. Thanks for pointing this
out.
Regards
Mike
TESSELLA Michael.OShea (AT) tessella (DOT) com
__/__/__/ Tessella Support Services plc
__/__/__/ 3 Vineyard Chambers, ABINGDON, OX14 3PX, England
__/__/__/ Tel: (44)(0)1235-555511 Fax: (44)(0)1235-553301
www.tessella.com Registered in England No. 1466429
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.