Home » SQL & PL/SQL » SQL & PL/SQL » unable to create table (oracle 10g R2 with winxp)
unable to create table [message #385123] Fri, 06 February 2009 08:48 Go to next message
dandeliondream
Messages: 12
Registered: February 2009
Junior Member
Please advise what is wrong with the following. I tried to run the program but in vain. I'm unable to create TOYSTORE table.

SET ECHO OFF
SET LINESIZE 200
SET WRAP OFF
PROMPT Create and load a sample database for Toydept and Toystore


CREATE TABLE TOYSTORE(
BARCODE number(13) not null,
NAME VARCHAR2(30),
PRICE DECIMAL(9,2),
QTY INT,
DEPTNUM INT,

CONSTRAINT TOYSTORE_PKEY PRIMARY KEY (BARCODE),
CONSTRAINT TOYSTORE_FKEY FOREIGN KEY (DEPTNUM) REFERENCES TOYDEPT(DEPTNUM),
ON DELETE SET DEFAULT
ON UPDATE CASCADE
);

INSERT INTO TOYSTORE VALUES(4325678912343, 'F-16 AIRPLANE', 567.88, 7, '0002');
INSERT INTO TOYSTORE VALUES(0000008912378, 'AIRWOLF', 622.98, 2, '0001');
INSERT INTO TOYSTORE VALUES(8122213323232, 'BIG BEAR', 233, 2, '0003');




CREATE TABLE TOYDEPT(
DEPTNUM number(4),
DNAME VARCHAR2(40),
CONSTRAINT TOYDEPT_PKEY PRIMARY KEY (DEPTNUM)
);

INSERT INTO TOYDEPT VALUES(0001, 'ESKY');
INSERT INTO TOYDEPT VALUES(0002, 'TWF');
INSERT INTO TOYDEPT VALUES(0003, 'SCREWS');


COMMIT;
PROMPT Done.
Re: unable to create table [message #385124 is a reply to message #385123] Fri, 06 February 2009 08:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There can be many reasons so use SQL*Plus and copy and paste your session.

Before please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel

[Updated on: Sat, 07 February 2009 01:02]

Report message to a moderator

Re: unable to create table [message #385128 is a reply to message #385123] Fri, 06 February 2009 09:16 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
You need to CREATE child table before creating parent table with FK constraint.
Re: unable to create table [message #385129 is a reply to message #385123] Fri, 06 February 2009 09:16 Go to previous messageGo to next message
pablolee
Messages: 2835
Registered: May 2007
Location: Scotland
Senior Member
As Michel says, many possible reasons. Surely it would seem sensible to post what errors you recieve rather than just saying that it isn't working as you expect?
Anyway, the immediate thing that I can see is that you are creating a foreign key to reference a table that doesn't exist (yet)
Re: unable to create table [message #385173 is a reply to message #385123] Fri, 06 February 2009 18:17 Go to previous messageGo to next message
dandeliondream
Messages: 12
Registered: February 2009
Junior Member
I'm usng oracle 10g R2 express edition

Error msg:


SQL> @createtoystore
Create and load a sample database for Toydept and Toystore

Table created.


1 row created.


1 row created.


1 row created.

SP2-0734: unknown command beginning "CONSTRAINT..." - rest of line ignored.
SP2-0734: unknown command beginning "CONSTRAINT..." - rest of line ignored.
SP2-0734: unknown command beginning "ON DELETE ..." - rest of line ignored.
SP2-0734: unknown command beginning "ON UPDATE ..." - rest of line ignored.
SP2-0044: For a list of known commands enter HELP
and to leave enter EXIT.
SP2-0042: unknown command ")" - rest of line ignored.
INSERT INTO TOYSTORE VALUES(4325678912343, 'F-16 AIRPLANE', 567.88, 7, '0002')
*
ERROR at line 1:
ORA-00942: table or view does not exist


INSERT INTO TOYSTORE VALUES(0000008912378, 'AIRWOLF', 622.98, 2, '0001')
*
ERROR at line 1:
ORA-00942: table or view does not exist


INSERT INTO TOYSTORE VALUES(8122213323232, 'BIG BEAR', 233, 2, '0003')
*
ERROR at line 1:
ORA-00942: table or view does not exist



Commit complete.

Done.
SQL> spool off
Re: unable to create table [message #385175 is a reply to message #385123] Fri, 06 February 2009 19:05 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
SQLPLUS does not like blank lines within a single SQL statement.


In the future please include:

SQL> SET TERM ON ECHO ON
Re: unable to create table [message #385183 is a reply to message #385173] Sat, 07 February 2009 01:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In addition:
Michel Cadot wrote on Fri, 06 February 2009 15:52
...

Before please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel


Re: unable to create table [message #385185 is a reply to message #385183] Sat, 07 February 2009 01:33 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Adding formatting or code-tags would have done nothing to improve the readability or clarity of the posts.
Re: unable to create table [message #385188 is a reply to message #385185] Sat, 07 February 2009 01:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I disagree and it couldn't hurt to do it and "in addition" it may (and will) help for future posts.

Regards
Michel

[Updated on: Sat, 07 February 2009 01:56]

Report message to a moderator

Re: unable to create table [message #385192 is a reply to message #385123] Sat, 07 February 2009 04:06 Go to previous messageGo to next message
dandeliondream
Messages: 12
Registered: February 2009
Junior Member
pleas eadvise which part is wrong I've removed the spaces but the problem persists.
Re: unable to create table [message #385202 is a reply to message #385123] Sat, 07 February 2009 09:59 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
You have SQL & can not figure out what is wrong.
We can not see what you are doing or EXACTLY how Oracle responds,
so we can offer no additional advice/guesses.
I do know that the SQL in 1st post can not generate the posted "output", but beyond that you need to following Posting Guidelines before additional help can be provided.

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you
Re: unable to create table [message #385208 is a reply to message #385123] Sat, 07 February 2009 11:05 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Keeping in mind what others already have said about the problem, could you, please, point to documentation which describes use of
ON DELETE SET DEFAULT
ON UPDATE CASCADE
you have used in your script?

I have searched the Oracle 10g documentation: Data integrity chapter and found that
Quote:
The referential actions supported by the FOREIGN KEY integrity constraints of Oracle are UPDATE and DELETE NO ACTION, and DELETE CASCADE.

Note:
Other referential actions not supported by FOREIGN KEY integrity constraints of Oracle can be enforced using database triggers.


Therefore, I have removed parts of the script that are invalid (unless you prove different) and - here is the result:
SQL> CREATE TABLE TOYDEPT
  2  (DEPTNUM NUMBER(4),
  3   DNAME VARCHAR2(40),
  4   CONSTRAINT TOYDEPT_PKEY PRIMARY KEY (DEPTNUM)
  5  );

Table created.

SQL> INSERT INTO TOYDEPT VALUES(0001, 'ESKY');

1 row created.

SQL> INSERT INTO TOYDEPT VALUES(0002, 'TWF');

1 row created.

SQL> INSERT INTO TOYDEPT VALUES(0003, 'SCREWS');

1 row created.

SQL> CREATE TABLE TOYSTORE
  2  (BARCODE NUMBER(13) NOT NULL,
  3   NAME VARCHAR2(30),
  4   PRICE DECIMAL(9,2),
  5   QTY INT,
  6   DEPTNUM INT,
  7   CONSTRAINT TOYSTORE_PKEY PRIMARY KEY (BARCODE),
  8   CONSTRAINT TOYSTORE_FKEY FOREIGN KEY (DEPTNUM)
  9       REFERENCES TOYDEPT(DEPTNUM)
 10  );

Table created.

SQL> INSERT INTO TOYSTORE VALUES(4325678912343, 'F-16 AIRPLANE', 567.88, 7, '0002');

1 row created.

SQL> INSERT INTO TOYSTORE VALUES(0000008912378, 'AIRWOLF', 622.98, 2, '0001');

1 row created.

SQL> INSERT INTO TOYSTORE VALUES(8122213323232, 'BIG BEAR', 233, 2, '0003');

1 row created.

SQL> COMMIT;

Commit complete.
Of course, parent table has to be created before a child one.
icon14.gif  Re: unable to create table [message #385217 is a reply to message #385208] Sat, 07 February 2009 19:32 Go to previous message
dandeliondream
Messages: 12
Registered: February 2009
Junior Member
Dear Littlefoot,

Thanks for pinpointing my mistake. The scripts are given to us as course materials so i assume they are correct. It's working now after revising my code. I'm very thankful for your help.

Previous Topic: How to compare two tables?
Next Topic: qery problem
Goto Forum:
  


Current Time: Thu Dec 08 14:16:03 CST 2016

Total time taken to generate the page: 0.32143 seconds