0

Hi Guys,
I need some help. I don't understand how to do an alter table statement.
I had a lab last week that used it and I couldn't get mine to work.

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)
);

--the alter statement
ALTER TABLE FACULTY
ADD CONSTRAINT Locid FOREIGN KEY (Locid) REFERENCES FACULTY (Locid);

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)
);
3
Contributors
2
Replies
3
Views
6 Years
Discussion Span
Last Post by alit2002
0

Try

alter table <table_name> modify (<column_name> <datatype>);
or
alter table <table_name> add (<column_name> <datatype>);

Watch out though. If you have data in the column you cant reduce it's size only increase it.

Alistair

Edited by alit2002: n/a

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.