Tutorial :Why does this table-creation script give me errors?



Question:

CREATE TABLE DEPARTMENTS (  dept_num                 NUMBER          (8)                                                   dept_name               VARCHAR2         (20) NOT NULL,             dept_loc                    VARCHAR2         (25),  dept_phone             CHAR                   (13),  CONSTRAINT dept_num_pk PRIMARY KEY (dept_num)  ) ;      CREATE TABLE JOB_HISTORY (  rank                              CHAR                 (20),                                            date_attendance        NUMERIC          (10) NOT NULL,  fac_num                      INTEGER         (8),    CONSTRAINT rank_pk PRIMARY KEY (rank),  CONSTRAINT fk_fac_num FOREIGN  KEY (fac_num) REFERENCES FACULTY (fac_num)  ) ;      CREATE TABLE FACULTY (  fac_num                        INTEGER      (8),                                                     fac_name                      CHAR             (15)      NOT NULL,  fac_last_name             CHAR              (15)      NOT NULL,  street                             VARCHAR2   (30),  city                                 VARCHAR2   (20),  state                              VARCHAR2    (2),  zip_code                       VARCHAR2    (10),  salary                            NUMERIC       (6,2),  earn_ytd                      NUMERIC       (6,2),  start_date                    DATE                          NOT NULL,  degree                          VARCHAR2      (15)   NOT NULL,  field                              CHAR                (15)   NOT NULL,  curr_rank                     CHAR                (20)  NOT NULL ,  dept_num                   NUMBER          (8),  CONSTRAINT fac_num_pk PRIMARY KEY (fac_num),  CONSTRAINT fk_curr_rank FOREIGN KEY (curr_rank) REFERENCES JOB_HISTORY (rank),  CONSTRAINT fk_dept_num FOREIGN KEY (dept_num)  REFERENCES DEPARTMENTS (dept_num)  );    CREATE TABLE SPEAKING_TOPICS (  code                         CHAR                    (10),                                                 title                          CHAR                    (20) NOT NULL,  last_given               CHAR                    (20),  time_given             NUMBER             (3),  fac_num_fke          INTEGER             (8),  CONSTRAINT code_pk PRIMARY KEY (code),  CONSTRAINT fk_fac_num  FOREIGN KEY (fac_num) REFERENCES FACULTY (fac_num)  ) ;      CREATE TABLE FACULTY ( fac_num INTERG ORA-00907: missing right parenthesis -    3 0.08 CREATE TABLE JOB_HISTORY ( rank  ORA-00907: missing right parenthesis -    4 0.09 CREATE TABLE SPEAKING_TOPICS ( code  ORA-00907: missing right parenthesis   


Solution:1

Some hints:

  • You can't use a precision with INTEGER. Use INTEGER instead of INTEGER(8). (INTEGER is actually the same as NUMBER(38), so it already has a precision.)

  • You're missing a comma after the dept_num column in the DEPARTMENTS table.

  • You can't create a foreign-key constraint onto a table that doesn't yet exist. The CREATE TABLE JOB_HISTORY ... statement attempts to create a foreign-key constraint onto the FACULTY table, but the FACULTY table hasn't been created at that point. Remove the constraint fk_fac_num from the CREATE TABLE JOB_HISTORY statement. After table FACULTY has been created, add the constraint using something like

    ALTER TABLE JOB_HISTORY ADD CONSTRAINT fk_fac_num ...  
  • There is no column called fac_num_fke in SPEAKING_TOPICS. Did you mean fac_num instead?

  • Constraints must all have different names. Change the name of the foreign-key constraint in SPEAKING_TOPICS to something else.

After making these changes to your SQL I was able to run it and create the tables successfully.


Solution:2

A corrected script is below, with comment blocks to explain what was wrong.

/*  A comma was missing  */  create table departments (    dept_num number(8)   ,dept_name varchar2(20) not null   ,dept_loc varchar2(25)   ,dept_phone char(13)   ,constraint dept_num_pk primary key(dept_num)  );    /*  Don't specify INTEGER(8)... just say INTEGER.  Also, you can't reference the FACULTY table before you've created it.   I've made that constraint into a separate command,  after FACULTY is created.  */  create table job_history(    rank char(20)                                             ,date_attendance numeric(10) not null   ,fac_num integer     ,constraint rank_pk primary key(rank)   --,constraint fk_fac_num foreign key (fac_num) references faculty(fac_num)  );    /*  Same issue with the INTEGER precision.  */  create table faculty(    fac_num integer                                                      ,fac_name char(15) not null   ,fac_last_name char(15) not null   ,street varchar2(30)   ,city varchar2(20)   ,state varchar2(2)   ,zip_code varchar2(10)   ,salary numeric(6,2)   ,earn_ytd numeric(6,2)   ,start_date date not null   ,degree varchar2(15) not null   ,field char(15) not null   ,curr_rank char(20) not null   ,dept_num number(8)   ,constraint fac_num_pk    primary key(fac_num)   ,constraint fk_curr_rank    foreign key(curr_rank)    references job_history(rank)   ,constraint fk_dept_num    foreign key(dept_num)    references departments(dept_num)  );    /*  This line is new.  JOB_HISTORY has to wait until FACULTY exists before  referencing it.  */  alter table job_history   add constraint fk_fac_num  foreign key(fac_num)  references faculty(fac_num);    /*  The INTEGER had a precision argument, again.  Also, the name of your foreign key on faculty(fac_num) was not unique; it   matched the one used by JOB_HISTORY to refer to faculty(fac_num).  I've modified the name of that constraint to make it unique.    Finally, the foreign_key had the wrong field name.  It needs to refer to the  local field name, whose name includes an "fke" @ the end  */  create table speaking_topics(    code char(10)                                                  ,title char(20) not null   ,last_given char(20)   ,time_given number(3)   ,fac_num_fke integer    ,constraint code_pk primary key(code)   ,constraint fk_spk_top_fac_num    foreign key(fac_num_fke)    references faculty(fac_num)  );    /*  If you want to DROP these tables, you must do so in a specific order,  since they reference each other.    You must also take an extra step to break the link btwn  JOB_HISTORY and FACULTY.    The below script will do it, but I've commented it out to avoid accidental  execution.  */    --drop table speaking_topics;  --alter table job_history drop constraint fk_fac_num;  --drop table faculty;  --drop table job_history;  --drop table departments;  


Solution:3

I'm not sure there is a datatype INTERGER.


Solution:4

You have a typo in FACULTY table definition: "INTERGER" instead of INTEGER

Same in JOB_HISTORY.


Solution:5

Oracle has no INTEGER data type. Use a NUMBER(10,0) which will hold a 32 bit integer like value.

Oracle has no NUMERIC data type, you need to use NUMBER (8,2).

Mostly this is a matter of typo's but Oracle is very picky about its table data types.


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »