November 12, 2010 at 10:45 am
Hello all,
I am working on my SQL project and don't understand what I am doing wrong. It might be that I am not adding foreign keys correctly and I'm geetong other errors like right parentesis missing but the table that I am trying to create doesn't require a parenthesis where the SQL is telling me where to put it. Here is my code so far
SPOOL lab3_output.txt
SET ECHO ON
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)
CONSTRAINTS 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
CONSTRAINT FACULTY_Locid_FK REFERENCES LOCATION,
Fphone NUMBER (10),
F_rank VARCHAR2 (4),
F_pin NUMBER (4)
);
ALTER TABLE FACULTY
ADD CONSTRAINT FACULTY_FRank_cc CHECK(F_rank IN ('ASSO','FULL','ASST','ADJ'));
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),
Szip NUMBER (9),
Sphone NUMBER (10) NOT NULL,
Sclass CHAR (2),
Sdob DATE NOT NULL,
S_pin NUMBER (4) NOT NULL,
Fid NUMBER (4)
CONSTRAINT STUDENT_FID_FK REFERENCES FACULTY
);
ALTER TABLE STUDENT
ADD CONSTRAINT STUDENT_Stu_cc CHECK(Sclass IN ('FR','SO','JR','SR','GR'));
CREATE TABLE TERM
(
Termid NUMBER (5)
CONSTRAINT TERM_Termid_PK PRIMARY KEY,
Tdesc VARCHAR2 (20) NOT NULL,
Status VARCHAR2 (20) NOT NULL
);
ALTER TABLE TERM
ADD CONSTRAINT Term_Stat_cc CHECK(Status IN ('CLOSED','OPEN'));
CREATE TABLE COURSE
(
Cid NUMBER (6)
CONSTRAINT COURSE_CID_PK PRIMARY KEY,
Callid VARCHAR2 (10) NOT NULL,
Cname VARCHAR2 (30) NOT NULL,
Ccredit NUMBER (2)
);
CREATE TABLE COURSE_SECTION
(
Csecid NUMBER (8)
Constraint COURSE_SECTION_Csecid_PK PRIMARY KEY,
Cid NUMBER (6) NOT NULL
Constraint COURSE_SECTION_CiD_FK REFERENCES COURSE,
Termid NUMBER (5) NOT NULL
Constraint COURSE_SECTION_TermiD_FK REFERENCES TERM,
Secnum NUMBER (2) NOT NULL,
Fid NUMBER (4)
Constraint COURSE_SECTION_FiD_FK REFERENCES FACULTY,
Day VARCHAR2(10),
Locid NUMBER (5)
Constraint COURSE_SECTION_LociD_FK REFERENCES LOCATION,
Maxenrl NUMBER (4) NOT NULL,
Currenrl NUMBER (4) NOT NULL
);
CREATE TABLE ENROLLMENT
(
Sid NUMBER(5)
CONSTRAINT ENROLLMENT_Sid_PK PRIMARY KEY REFERENCES STUDENT,
Csecid NUMBER(8)
ADD CONSTRAINT ENROLLMENT_Csecid_FK FOREIGN KEY REFERENCES COURSE,
Grade CHAR(1)
);
ALTER TABLE ENROLLMENT
ADD CONSTRAINT ENROLLMENT_Grade_cc CHECK(Grade IN ('A','B','C','D','F','I','W'));
I am new to this so please bare with me as this is the first time i have worked with SQL. Oh yeah by the way this is done using Oracle SQL. Thanks for looking at my post.
November 12, 2010 at 10:51 am
First, are you using Oracle? The "CASCADE CONSTRAINTS PURGE" is an Oracle extension for Drop Table, and isn't part of T-SQL.
If you're using MS SQL Server, you'll need to change a few things in your syntax, including that. Need to know which you're using before going any further.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 12, 2010 at 11:06 am
Yes I am using Oracle.
November 12, 2010 at 11:32 am
In that case, you'll probably be better off on an Oracle forum. This site is dedicated mostly to MS SQL.
I'm not familiar with Oracle myself, but you could post it in the Working With Oracle forum here, and someone who is might be able to help there.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 12, 2010 at 12:11 pm
Ok thanks.
November 12, 2010 at 1:57 pm
Do post on an Oracle support site as well. You're much more likely to get good help on a site with more Oracle devs and DBAs than on this site.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply