0

hi everyone I'm completely new to SQL and I'm trying to answer this question in my book:

7.5 Write a CREATE TABLE statement for the EMPLOYEE table. Email is required and is an alternate key, and the default value of Department is Human Resources. Cascade updates but not deletions from DEPARTMENT to EMPLOYEE.

I'm running the query in Oracle iSQL*Plus, I successfully created the department table, but when i tried creating the employee table while meeting these requirements I get the missing keyword error at line 12

constraint DepartmentFK FOREIGN KEY(DepartmentName),

this is the whole query (I dropped the department table before attempting the whole thing, and it still gives the same error)

CREATE TABLE DEPARTMENT (
DepartmentName  char(35) NOT NULL,
BudgetCode      char(30) NOT NULL,
OfficeNumber    char(15) NOT NULL,
Phone           char(12) NOT NULL,

Constraint DepartmentPK PRIMARY KEY(DepartmentName)

);

CREATE TABLE EMPLOYEE (
ProjectID   int      NOT NULL,
Name        char(30) NOT NULL,
Department  char(15) NOT NULL,
MaxHours    int      NOT NULL,
StartDate   char(8)  NULL,
EndDate     char(8)  NULL,
Email       char(30) DEFAULT 'Human Resources' NOT NULL,

Constraint EmployeePK   PRIMARY KEY(ProjectID),
Constraint EmployeeAK1 UNIQUE(Email),
constraint DepartmentFK FOREIGN KEY(DepartmentName),
 references DEPARTMENT(DepartmentName)
 ON UPDATE CASCADE 
 ON DELETE no ACTION 

);

I tried following the most similar example in the book and looking up foreign key constraint and references but i can't understand why I'm getting this error...

EDIT:

I took out the comma but i still got these two errors:

CREATE TABLE DEPARTMENT (
             *
ERROR at line 1: 
ORA-00955: name is already used by an existing object 
 ON UPDATE CASCADE
    *
ERROR at line 14: 
ORA-00905: missing keyword
Mr.AwesomeSauce
  • 27
  • 1
  • 3
  • 8
  • take a reference from here: http://stackoverflow.com/questions/48772/how-do-i-create-a-foreign-key-in-sql-server?answertab=votes#tab-top – Suraj Shrestha Mar 09 '14 at 04:16
  • `On UPDATE CASCADE` is not available in Oracle. And you're getting error for Department table, simply cause it already exists. – Incognito Mar 09 '14 at 04:21
  • thank you very much.. do you know how i can duplicate this in oracle? – Mr.AwesomeSauce Mar 09 '14 at 04:22
  • You would need to understand the concept on `ON UPDATE CASCADE` for clarity, and then refer to http://www.orafaq.com/wiki/Foreign_key#ON_UPDATE_CASCADE to understand how to implement this concept in Oracle. `ON DELETE CASCADE` is available in Oracle. – Incognito Mar 09 '14 at 04:24

1 Answers1

1

You have to remove the comma you have used after 

constraint DepartmentFK FOREIGN KEY(DepartmentName),

This is one unit

constraint DepartmentFK FOREIGN KEY(DepartmentName) references DEPARTMENT(DepartmentName)

EDIT: Since, you have edited your question -

ON UDDATE CASCADE option is not available in Oracle Database, and that's why you're getting an error.

You're getting an error for Department table, since the table already exists, probably from the last run!

Incognito
  • 2,964
  • 2
  • 27
  • 40