| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
03-05-03, 09:47
|
|
Super Moderator
|
|
Join Date: Feb 2002
Location: Hampshire, UK
Posts: 441
|
|
|
Error...
|
|
Code:
create TABLE Student (
student_id NUMBER NOT NULL PRIMARY KEY,
pps_id NUMBER NOT NULL FOREIGN KEY pps(pps_id) REFERENCES pps(pps_id),
p_tutor NUMBER NOT NULL FOREIGN KEY REFERENCES tutor(tutor_id),
level NUMBER NOT NULL FOREIGN KEY REFERENCES ac_level(a_level),
surname VARCHAR2 (35) NOT NULL,
forename VARCHAR2 (35) NOT NULL,
initials VARCHAR2 (1),
title VARCHAR2 (4) NOT NULL,
gender VARCHAR2 (1) NOT NULL,
dob DATE NOT NULL,
address VARCHAR2 (50) NOT NULL,
address2 VARCHAR2 (50) NOT NULL,
city VARCHAR2 (30) NOT NULL,
postcode VARCHAR2 (10) NOT NULL,
telephone_home VARCHAR2 (20),
telephone_mobile VARCHAR2 (20),
email VARCHAR2 (255),
next_of_kin vARCHAR2 (50) NOT NULL,
emergency_contact VARCHAR2 (20) NOT NULL,
application_date DATE NOT NULL,
comments VARCHAR2 (4000));
resulting in:
Quote:
pps_id NUMBER NOT NULL FOREIGN KEY pps(pps_id) REFERENCES pps(pps_id),
*
ERROR at line 3:
ORA-00907: missing right parenthesis
|
(I think this is oracle not my sql???)
Anyway - I need to get this problem solved quickly if possible; it is likely to be me just missing something obvious!
|
|
03-05-03, 10:04
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,540
|
|
when you say --
pps_id NUMBER NOT NULL FOREIGN KEY pps(pps_id) REFERENCES pps(pps_id)
you are using the "inline" definition, i.e. you are appending the FK clause to the pps_id column definition, so the pps(pps_id) is redundant (and wrong)
you should say instead --
pps_id NUMBER NOT NULL FOREIGN KEY REFERENCES pps(pps_id)
similar to the two FKs which follow
rudy
|
|
03-05-03, 10:10
|
|
Registered User
|
|
Join Date: Dec 2002
Location: Netherlands
Posts: 118
|
|
|
|
Hi
"pps_id NUMBER NOT NULL FOREIGN KEY REFERENCES pps(pps_id)"
Doesn't work, pasted some examples that must make it work for you.
[REMIVISSER@DB1.ORA-0000.COM] CREATE TABLE MASTER (K NUMBER PRIMARY KEY);
Table created.
[REMIVISSER@DB1.ORA-0000.COM] CREATE TABLE CHILD (K NUMBER REFERENCES MASTER(K));
Table created.
[REMIVISSER@DB1.ORA-0000.COM] DROP TABLE CHILD;
Table dropped.
[REMIVISSER@DB1.ORA-0000.COM] CREATE TABLE CHILD (K NUMBER );
Table created.
[REMIVISSER@DB1.ORA-0000.COM] ALTER TABLE CHILD
2 ADD CONSTRAINT FK_CHILD_MASTER
3 FOREIGN KEY (K) REFERENCES MASTER(K);
Table altered.
[REMIVISSER@DB1.ORA-0000.COM] DROP TABLE CHILD;
Table dropped.
[REMIVISSER@DB1.ORA-0000.COM] CREATE TABLE CHILD (K NUMBER FOREIGN KEY REFERENCES MASTER(K) );
CREATE TABLE CHILD (K NUMBER FOREIGN KEY REFERENCES MASTER(K) )
*
ERROR at line 1:
ORA-00907: missing right parenthesis
Good Luck
Remi
|
|
03-05-03, 10:24
|
|
Super Moderator
|
|
Join Date: Feb 2002
Location: Hampshire, UK
Posts: 441
|
|
damn thats what it is...!! ARG. Will post back and tell you if it works! thanks guys!
|
|
03-05-03, 10:29
|
|
Super Moderator
|
|
Join Date: Feb 2002
Location: Hampshire, UK
Posts: 441
|
|
right it didn't work. Is it because it can't reference that table (it is further down the script) - if so it should really say. Oracle, pshh. Anyway if it is; do i have to add this reference foreign key after all tables are made??
Code:
SQL> create TABLE Student (
2 student_id NUMBER NOT NULL PRIMARY KEY,
3 pps_id NUMBER NOT NULL FOREIGN KEY REFERENCES pps(pps_id),
4 p_tutor NUMBER NOT NULL FOREIGN KEY REFERENCES tutor(tutor_id),
5 level NUMBER NOT NULL FOREIGN KEY REFERENCES ac_level(a_level),
6 surname VARCHAR2 (35) NOT NULL,
7 forename VARCHAR2 (35) NOT NULL,
8 initials VARCHAR2 (1),
9 title VARCHAR2 (4) NOT NULL,
10 gender VARCHAR2 (1) NOT NULL,
11 dob DATE NOT NULL,
12 address VARCHAR2 (50) NOT NULL,
13 address2 VARCHAR2 (50) NOT NULL,
14 city VARCHAR2 (30) NOT NULL,
15 postcode VARCHAR2 (10) NOT NULL,
16 telephone_home VARCHAR2 (20),
17 telephone_mobile VARCHAR2 (20),
18 email VARCHAR2 (255),
19 next_of_kin vARCHAR2 (50) NOT NULL,
20 emergency_contact VARCHAR2 (20) NOT NULL,
21 application_date DATE NOT NULL,
22 comments VARCHAR2 (4000));
pps_id NUMBER NOT NULL FOREIGN KEY REFERENCES pps(pps_id),
*
ERROR at line 3:
ORA-00907: missing right parenthesis
|
|
03-05-03, 10:31
|
|
Super Moderator
|
|
Join Date: Feb 2002
Location: Hampshire, UK
Posts: 441
|
|
well it's not that the table pps does not exist (i have created it now). hmm. HELP!
|
|
03-05-03, 10:32
|
|
Registered User
|
|
Join Date: Dec 2002
Location: Netherlands
Posts: 118
|
|
See
[REMIVISSER@DB1.ORA-0000.COM] CREATE TABLE CHILD (K NUMBER FOREIGN KEY REFERENCES MASTER(K) );
CREATE TABLE CHILD (K NUMBER FOREIGN KEY REFERENCES MASTER(K) )
*
ERROR at line 1:
ORA-00907: missing right parenthesis
What I would advice you is to first create the table and later add the constraint.
I think it's easier...
I would also advice you to name your constraint (when you don't name it Oracle will assign a name)
Naming your constraints (and add some logic to the naming) will make it easier for your to look the constraints up in dba_objects.
Good luck
Remi
|
|
03-05-03, 10:42
|
|
Super Moderator
|
|
Join Date: Feb 2002
Location: Hampshire, UK
Posts: 441
|
|
right, removed all the stuff and got this;
Code:
create TABLE Student (
student_id NUMBER NOT NULL PRIMARY KEY,
pps_id NUMBER NOT NULL,
p_tutor NUMBER NOT NULL,
a_level NUMBER NOT NULL,
surname VARCHAR2 (35) NOT NULL,
forename VARCHAR2 (35) NOT NULL,
initials VARCHAR2 (1),
title VARCHAR2 (4) NOT NULL,
gender VARCHAR2 (1) NOT NULL,
dob DATE NOT NULL,
address VARCHAR2 (50) NOT NULL,
address2 VARCHAR2 (50) NOT NULL,
city VARCHAR2 (30) NOT NULL,
postcode VARCHAR2 (10) NOT NULL,
telephone_home VARCHAR2 (20),
telephone_mobile VARCHAR2 (20),
email VARCHAR2 (255),
next_of_kin vARCHAR2 (50) NOT NULL,
emergency_contact VARCHAR2 (20) NOT NULL,
application_date DATE NOT NULL,
comments VARCHAR2 (4000));
The above works fine (apart from level which seems to be special - so i changed it), but the constraint (bellow) doesn't;
Code:
SQL> ALTER TABLE Student
2 ADD CONSTRAINT FK_Student_pps
3 FOREIGN KEY REFERENCES pps(pps_id);
FOREIGN KEY REFERENCES pps(pps_id)
*
ERROR at line 3:
ORA-00906: missing left parenthesis
Any ideas???
fyi: I am using notepad to write queries - I have been told there are sometimes problems with spurious characters but don't know the truth in that
|
Last edited by rhs98; 03-05-03 at 10:45.
|
03-05-03, 10:53
|
|
Registered User
|
|
Join Date: Dec 2002
Location: Netherlands
Posts: 118
|
|
Notepad is fine.
3 FOREIGN KEY REFERENCES pps(pps_id);
Should change to
3 FOREIGN KEY (foreign_key_column_name) REFERENCES pps(pps_id);
How should Oracle know what column has a foreign key ;-)
Hope this helps you out.
Remi
http://askremi.ora-0000.com
|
|
03-05-03, 10:55
|
|
Super Moderator
|
|
Join Date: Feb 2002
Location: Hampshire, UK
Posts: 441
|
|
it was stareing me in the face! Many thanks - It seems to be working noice now!
Cheers!
|
|
03-05-03, 10:58
|
|
Super Moderator
|
|
Join Date: Feb 2002
Location: Hampshire, UK
Posts: 441
|
|
I can't spell for toast 
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
Error...
