| Re: primary/foreign key pleeeeeease help [message #375094] |
Wed, 25 July 2001 08:11 |
Sudhakar Atmakuru
Messages: 58
Registered: May 2001
|
Member |
|
|
Lars,
I guess, you have given the data type CHAR(4) for CODE in the CODES table. That causes to give the length of that column always 4, but not depending the value stored. Please check it up and change it to VARCHAR2(4) for CODE column in the CODES TABLE. I tried your example and it works perfectly alright.
Look at the following code sequence what I just created with your data and how it prints out the output.
SQL> CREATE TABLE ITEMS (
2 ITEM NUMBER NOT NULL PRIMARY KEY,
3 PRODCODE CHAR(4) NOT NULL);
Table created.
SQL> CREATE TABLE CODES (
2 CODE VARCHAR2(4) NOT NULL PRIMARY KEY,
3 DESCR VARCHAR2(15) NOT NULL);
Table created.
SQL> INSERT INTO ITEMS VALUES(&ITEM,'&PRODCODE');
Enter value for item: 1
Enter value for prodcode: ABCD
old 1: INSERT INTO ITEMS VALUES(&ITEM,'&PRODCODE')
new 1: INSERT INTO ITEMS VALUES(1,'ABCD')
1 row created.
SQL> R
1* INSERT INTO ITEMS VALUES(&ITEM,'&PRODCODE')
Enter value for item: 2
Enter value for prodcode: EFGH
old 1: INSERT INTO ITEMS VALUES(&ITEM,'&PRODCODE')
new 1: INSERT INTO ITEMS VALUES(2,'EFGH')
1 row created.
SQL> R
1* INSERT INTO ITEMS VALUES(&ITEM,'&PRODCODE')
Enter value for item: 3
Enter value for prodcode: IJKL
old 1: INSERT INTO ITEMS VALUES(&ITEM,'&PRODCODE')
new 1: INSERT INTO ITEMS VALUES(3,'IJKL')
1 row created.
SQL> INSERT INTO CODES VALUES('&CODE','&DESCR');
Enter value for code: ABC
Enter value for descr: FACTORY
old 1: INSERT INTO CODES VALUES('&CODE','&DESCR')
new 1: INSERT INTO CODES VALUES('ABC','FACTORY')
1 row created.
SQL> R
1* INSERT INTO CODES VALUES('&CODE','&DESCR')
Enter value for code: ABCD
Enter value for descr: VALVE
old 1: INSERT INTO CODES VALUES('&CODE','&DESCR')
new 1: INSERT INTO CODES VALUES('ABCD','VALVE')
1 row created.
SQL> R
1* INSERT INTO CODES VALUES('&CODE','&DESCR')
Enter value for code: EFG
Enter value for descr: PLANT
old 1: INSERT INTO CODES VALUES('&CODE','&DESCR')
new 1: INSERT INTO CODES VALUES('EFG','PLANT')
1 row created.
SQL> R
1* INSERT INTO CODES VALUES('&CODE','&DESCR')
Enter value for code: EFGH
Enter value for descr: PUMP
old 1: INSERT INTO CODES VALUES('&CODE','&DESCR')
new 1: INSERT INTO CODES VALUES('EFGH','PUMP')
1 row created.
SQL> SELECT * FROM ITEMS;
ITEM PROD
---------- ----
1 ABCD
2 EFGH
3 IJKL
SQL> SELECT * FROM CODES;
CODE DESCR
---- ---------------
ABC FACTORY
ABCD VALVE
EFG PLANT
EFGH PUMP
SQL> SELECT A.ITEM,B.DESCR FROM ITEMS A, CODES B
2 WHERE LENGTH(B.CODE)=3 AND B.CODE=SUBSTR(A.PRODCODE,1,3);
ITEM DESCR
---------- ---------------
1 FACTORY
2 PLANT
SQL> SPOOL OFF
When I created the CODES table with CODE CHAR(4) it printed the LENGTH(CODE) always 4 no matter what it has stored. So I changed the table definition with CODE VARCHAR2(4), then it worked good.
If you find your CODES table created with CODE CHAR(4) then create a temporary table with new VARCHAR2(4) definition and then dump the original data from the master table, drop the first and rename the new to first like .
CREATE TABLE CODES_TEMP (
CODE VARCHAR2(4)
... your other items as created in CODES);
INSERT INTO CODES_TEMP SELECT * FROM CODES;
DROP TABLE CODES;
RENAME CODE_TEMP TO CODES;
COMMIT;
Try it out and let me know immediately how it worked.
:)
Good luck.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
