Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2006
    Posts
    2

    Unanswered: help - OCA-30035: column not found - schoolboy error

    I have just started studying oracle 8i for a Uni module

    I am at the point where I am learning about constraints. I dropped all my tables and attempted to rebuild them with constraints but I cannot create them again. It is probably a very simple error. The only table that I can create at present is the ALLOC table. Please see the code listed below. All help very greatfully recieved

    Code:
     CREATE TABLE EMP(
     EMP_NO CHAR(2)
    	CONSTRAINT PKEY_EMP PRIMARY KEY,
     EMP_NAME CHAR(10),
     SALARY INTEGER CONSTRAINT MIN_SALARY CHECK (SAL >=6000),
     MARITAL_STATUS CHAR(1)
    	CONSTRAINT MAR_STATUS CHECK (MAR_STATUS IN ('S','M','W','D')));
    
     CREATE TABLE DEPT(
     DEPT_NO CHAR(2)
    	CONSTRAINT PKEY_DEPT PRIMARY KEY,
     DEPT_NAME CHAR(10)
    	CONSTRAINT UNIQ_DEPT_NAME UNIQUE,
     MANAGER_NO CHAR(2),
     BUDGET INTEGER
    	CONSTRAINT MAX_SALARY CHECK (SAL <=400000));
    
    CREATE TABLE PROJ(
    PROJ_NO CHAR(2)
    	CONSTRAINT PKEY_PROJ PRIMARY KEY,
    DEPT_NO CHAR(2)
    	CONSTRAINT PROJ_DEPT REFERENCES DEPT,
    START_DATE DATE CONSTRAINT PROJ_DATE CHECK (START_DATE >='1-JAN-1998')
    DEADLINE DATE CONSTRAINT DEADLINE CHECK (DEADLINE >=START_DATE));
    
    CREATE TABLE ALLOC(
    EMP_NO CHAR(2),
    PROJ_NO CHAR(2),
    CONSTRAINT PKEY_ALLOC PRIMARY KEY (EMP_NO,PROJ_NO));

  2. #2
    Join Date
    Oct 2002
    Location
    Plymouth UK
    Posts
    116
    There a few inconsistencies with your column names in your code and the reference to dept requires the filed name as well. I have appended your code with these corrections:

    Code:
     CREATE TABLE EMP(
     EMP_NO CHAR(2)
    	CONSTRAINT PKEY_EMP PRIMARY KEY,
     EMP_NAME CHAR(10),
     SALARY INTEGER CONSTRAINT MIN_SALARY CHECK (SALARY >=6000),
     MARITAL_STATUS CHAR(1)
    	CONSTRAINT MAR_STATUS CHECK (MARITAL_STATUS IN ('S','M','W','D')));
    
     CREATE TABLE DEPT(
     DEPT_NO CHAR(2)
    	CONSTRAINT PKEY_DEPT PRIMARY KEY,
     DEPT_NAME CHAR(10)
    	CONSTRAINT UNIQ_DEPT_NAME UNIQUE,
     MANAGER_NO CHAR(2),
     BUDGET INTEGER
    	CONSTRAINT MAX_SALARY CHECK (BUDGET <=400000));
    
    CREATE TABLE PROJ(
    PROJ_NO CHAR(2)
    	CONSTRAINT PKEY_PROJ PRIMARY KEY,
    DEPT_NO CHAR(2)
    	CONSTRAINT PROJ_DEPT REFERENCES DEPT(DEPT_NO),
    START_DATE DATE CONSTRAINT PROJ_DATE CHECK (START_DATE >='1-JAN-1998')
    DEADLINE DATE CONSTRAINT DEADLINE CHECK (DEADLINE >=START_DATE));

  3. #3
    Join Date
    Mar 2006
    Posts
    2

    aaah thank you

    After posting this request for help I read a few more posts, then realised I must appear lazy and look blike I started crying for help at the first hurdle.

    But your help was very useful I now see that I was confusing my column names with constraint names. Very obvious now. But It has really helped me get my head around a basic principle.

    Thank You VERY MUCH!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •