New to SQL DDL - Having Foreign Key issues 3

[eoinmoon]

Hi,

I am trying to code my database [Oracle], but am coming across a problem that I cannot figure out. The code below gives the following error

Table dropped.
Table created.
Table created.

CONSTRAINT ACCOM_res_id_fk FOREIGN KEY (res_id) REFERENCES RESORT (res_id
*
ERROR at line 11:
ORA-00904: "RES_ID": invalid identifier

I have tried all I know and probably it is something simple but I cannot spot why I am getting this error. The table is created that provides the foreign key but it does not seem
to recognize it . I have made it upper case / lower case / renamed VARCHAR2 to CHAR/ removed the constraint on the RESORT primary key/taking out underscores... but the same error crops up.

The Code:

Drop TABLE accom;
Drop TABLE resort;

CREATE TABLE RESORT
(
res_id NUMBER (10) NOT NULL,
res_name VARCHAR2 (20) NOT NULL,
res_country VARCHAR2 (20) NOT NULL,
res_elav NUMBER (7),
res_numlifts NUMBER (3),
res_num_grn NUMBER (3),
res_num_red NUMBER (3),
res_num_blk NUMBER (3),
CONSTRAINT RESORT_res_id_pk PRIMARY KEY (res_id)
) ;


CREATE TABLE ACCOM
(
accom_id NUMBER(10) NOT NULL,
accom_name VARCHAR2 (20) NOT NULL,
accom_addr VARCHAR2 (100) NOT NULL,
accom_cap NUMBER(4) NOT NULL,
accom_avail NUMBER(4) NOT NULL,
accom_disc NUMBER(3) NOT NULL,
accom_rate NUMBER(3) NOT NULL,
CONSTRAINT ACCOM_accom_id_pk PRIMARY KEY (accom_id),
CONSTRAINT ACCOM_res_id_fk FOREIGN KEY (res_id) REFERENCES RESORT (res_id)
);

thanks for any help
Eoin

 

[PHV]

FOREIGN KEY (res_id)
res_id is not a column of the table accom ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[r937]

you are trying to declare a foreign key on a column that doesn't exist in the table

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[eoinmoon]

Thank you for the response, but being new to this the next question will probably irritate you.

This is code that works [example code]… how is this different from what I am trying to do?

CREATE TABLE DEPT (
DEPTNO NUMBER(2) NOT NULL,
DNAME CHAR(14),
LOC CHAR(13),
CONSTRAINT DEPT_PRIMARY_KEY PRIMARY KEY (DEPTNO));

CREATE TABLE EMP (
EMPNO NUMBER(4) NOT NULL,
ENAME CHAR(10),
JOB CHAR(9),
MGR NUMBER(4)
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2) NOT NULL,
CONSTRAINT EMP_FOREIGN_KEY FOREIGN KEY (DEPTNO) REFERENCES DEPT (DEPTNO),
CONSTRAINT EMP_PRIMARY_KEY PRIMARY KEY (EMPNO));

REgards

Eoin

 

[r937]

what's different is that the column you are declaring as a foreign key in the EMP table actually exists in the EMP table

you do not have a column called res_is in the ACCOM table

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[SQLSister]

When you havea primary key foreign key relationship, the field must exist in both tables. In your code which is not working it does not exist in the foriegn key table. In the working code, the key field is part of the table definition.

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[r937]

looking at your columns, the only one that makes any sense as the foreign key would be accom_id

so try this --

CONSTRAINT ACCOM_res_id_fk
FOREIGN KEY (accom_id) REFERENCES RESORT (res_id)


rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[PHV]

What is supposed to be the relationship between resort and accom ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[eoinmoon]

Thanks everyone for the help , it was that I had no column name to reference the foreign key in the table as SQLSister pointed out.

Thanks again for the quick response , new to SQL and databases and learning all of the time.

Regards
Eoin

 

[r937]

FYI, three different people pointed it out, not just one

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)