1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

alter table question

Discussion in 'SQL PL/SQL' started by dan, Nov 15, 2010.

  1. dan

    dan Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    Hello all,
    I'm in the (painful) process of trying to grow on the things I am doing, and have decided in my later life to further my education.
    I am in a database class and having trouble. I lost my rear on a lab, I kept getting errors on my script. If you could please take a look and maybe you can help, because the textbook wasn't much help.
    Code (Text):



    DROP TABLE ENROLLMENT CASCADE CONSTRAINTS PURGE;
    DROP TABLE COURSE_SECTION CASCADE CONSTRAINTS PURGE;
    DROP TABLE COURSE CASCADE CONSTRAINTS PURGE;
    DROP TABLE TERM CASCADE CONSTRAINTS PURGE;
    DROP TABLE STUDENT CASCADE CONSTRAINTS PURGE;
    DROP TABLE FACULTY CASCADE CONSTRAINTS PURGE;
    DROP TABLE LOCATION CASCADE CONSTRAINTS PURGE;

    CREATE TABLE LOCATION
    (
    Locid NUMBER(5) CONSTRAINT LOCATION_Locid_pk PRIMARY KEY,
    Bldg_Code VARCHAR2(10) NOT NULL,
    Room VARCHAR2(6) NOT NULL,
    Capacity NUMBER(5)
    );

    CREATE TABLE FACULTY
    (
    Fid NUMBER(4) CONSTRAINT FACULTY_Fid_pk PRIMARY KEY,
    Flname VARCHAR2(25) NOT NULL,
    Ffname VARCHAR2(25) NOT NULL,
    Fmi CHAR(1),
    Locid NUMBER(5) NOT NULL,
    Fphone NUMBER(10),
    F_rank VARCHAR2(4),
    F_pin NUMBER(4)
    );

    ALTER TABLE FACULTY
    ADD FOREIGN KEY (Locid) REFERENCES SECTION_COURSE;

    CREATE TABLE STUDENT
    (
    Sid NUMBER(5) CONSTRAINT STUDENT_Sid_pk PRIMARY KEY,
    Slname VARCHAR2(25) NOT NULL,
    Sfname VARCHAR2(25) NOT NULL,
    Smi Char(1),
    Saddr VARCHAR2(30),
    Scity VARCHAR2(30),
    Sstate CHAR(2) DEFAULT 'WI',
    Szip NUMBER(9),
    Sphone NUMBER(10) NOT NULL,
    Sclass CHAR(2) DEFAULT 'FR',
    Sdob DATE NOT NULL,
    S_pin NUMBER(4) NOT NULL,
    Fid NUMBER(4)
    );



    CREATE TABLE TERM
    (
    Termid NUMBER(5) CONSTRAINT TERM_Termid_pk PRIMARY KEY,
    Tdesc VARCHAR2(20) NOT NULL,
    Status VARCHAR2(20) NOT NULL
    );

    CREATE TABLE COURSE
    (
    Cid NUMBER(6) CONSTRAINT COURSE_Cid_pk PRIMARY KEY,
    Callid VARCHAR(10) NOT NULL,
    Cname VARCHAR2(30) NOT NULL,
    Ccredit NUMBER(2) DEFAULT 3
    );

    CREATE TABLE COURSE_SECTION
    (
    Csecid NUMBER(8) CONSTRAINT COURSESECTION_Csecid_pk PRIMARY KEY,
    Cid NUMBER(6) NOT NULL,
    Termid NUMBER(5) NOT NULL,
    Secnum NUMBER(2) NOT NULL,
    Fid NUMBER(4),
    Day VARCHAR2(10),
    Locid NUMBER(5),
    Maxenrl NUMBER(4) NOT NULL,
    Currenrl NUMBER(4) NOT NULL
    );

    CREATE TABLE ENROLLMENT
    (
    Sid NUMBER(5) CONSTRAINT ENROLLMENT_Sid_fk REFERENCES STUDENT,
    Csecid NUMBER(5) CONSTRAINT ENROLLMENT_Csecid_fk REFERENCES COURSE_SECTION,
    Grade CHAR(1),
    CONSTRAINT ENROLLMENT_Sid_Csecid_pk PRIMARY KEY (Sid, Csecid)
    );






     
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You cannot create a foreign key referencing a table which doesn't yet exist (or won't ever exist as you have it written). The COURSE_SECTION table doesn't exist when you first attempt to create a foreign key constraint; you also have the table name coded as SECTION_COURSE, naming a table which will never exist according to your script. Move the ALTER TABLE statement creating the foreign key to the end of your script, reference the table correctly and you'll have no errors.
     
  3. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Hi Dan,

    Please always remember to mention the error codes and error messages when you post a problem instead of saying "I kept getting errors on my script". This will help others to find and help you to fix the problem.