Hey Guys...first time really using SQLPLUS (Oracle)... and im having trouble...
ID: 3638261 • Letter: H
Question
Hey Guys...first time really using SQLPLUS (Oracle)... and im having trouble... First heres some of my tablesIt throws errors saying about parentheses when i put the ; at the end of each.. but when i remove that and then try to say ALTER and drop a column it tells me its out of view or something...
CREATE TABLE Tech_personnel
( pplSoft NUMBER(7) NOT NULL,
fname VARCHAR2(20),
lname VARCHAR2(20),
pittID NUMBER(6) NOT NULL,
expertise VARCHAR2(20),
office_phone VARCHAR2(20),
CONSTRAINT tech_pk PRIMARY KEY (pplSoft),
CONSTRAINT tech_ak ALTERNATIVE KEY (pittId)
);
CREATE TABLE Users
( pplSoft NUMBER(7) NOT NULL,
fname VARCHAR2(20),
lname VARCHAR2(20),
pittID NUMBER(6) NOT NULL,
office_phone VARCHAR2(20),
CONSTRAINT User_Pk PRIMARY KEY (pplSoft),
CONSTRAINT User_Ak ALTERNATIVE KEY (pittID)
);
CREATE TABLE Categories
(
category_id VARCHAR2(12) NOT NULL,
category VARCHAR2(20) NOT NULL,
description VARCHAR2(30),
CONSTRAINT Categories_PK PRIMARY KEY (category_id),
CONSTRAINT Categories_AK ALTERNATIVE KEY (category)
);
Explanation / Answer
You cant have Alternate keys in oracle, its just conceptual. So the database is giving an error since it doesnt have a keyword named as ALTERNATE KEY So the queries will become. CREATE TABLE Tech_personnel ( pplSoft NUMBER(7) NOT NULL, fname VARCHAR2(20), lname VARCHAR2(20), pittID NUMBER(6) NOT NULL, expertise VARCHAR2(20), office_phone VARCHAR2(20), CONSTRAINT tech_pk PRIMARY KEY (pplSoft)); CREATE TABLE Users ( pplSoft NUMBER(7) NOT NULL, fname VARCHAR2(20), lname VARCHAR2(20), pittID NUMBER(6) NOT NULL, office_phone VARCHAR2(20), CONSTRAINT User_Pk PRIMARY KEY (pplSoft)); CREATE TABLE Categories ( category_id VARCHAR2(12) NOT NULL, category VARCHAR2(20) NOT NULL, description VARCHAR2(30), CONSTRAINT Categories_PK PRIMARY KEY (category_id));