7 Replies - 511 Views - Last Post: 24 October 2013 - 07:08 AM Rate Topic: -----

#1 java_11  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 28
  • Joined: 09-July 13

MySQL CODE ERROR

Posted 23 October 2013 - 07:44 AM

I Have already created this table.

CREATE TABLE Staff (Username INTEGER PRIMARY KEY, Name VARCHAR(20) NOT NULL, RoomNumber INTEGER CHECK (Age BETWEEN 1 AND 20) );


The question is - Create the Courses table in a similar way, but also set CourseLeader to be a foreign key as specified. Add an action to the foreign key constraint: when the parent is deleted, this value wants to be set to null.

This is what I have typed -

CREATE  TABLE  Courses( CourseID INTEGER PRIMARY KEY, Title VARCHAR(50)  NOT  NULL, CourseLeader VARCHAR(15) NOT NULL, Primary Key, FOREIGN KEY (Name) REFERENCES Staff (USERNAME) ON DELETE SET NULL));


Can someone tell me what im doing wrong and also type in the right code.

This is not an assignment, just an exercise.

Any questions then please ask.

three tables:

Staff (Username, Name, RoomNumber)
Courses (CourseID, Title, CourseLeader)
Classes (ClassID, CourseID, Tutor, Location, Trimester, Day, Time, Hours)

This post has been edited by Atli: 23 October 2013 - 07:59 AM
Reason for edit:: Use [code] tags when posting code.


Is This A Good Question/Topic? 0
  • +

Replies To: MySQL CODE ERROR

#2 modi123_1  Icon User is offline

  • Suitor #2
  • member icon



Reputation: 9197
  • View blog
  • Posts: 34,551
  • Joined: 12-June 08

Re: MySQL CODE ERROR

Posted 23 October 2013 - 07:47 AM

Is there an error? If so what?
Was This Post Helpful? 0
  • +
  • -

#3 DarenR  Icon User is offline

  • D.I.C Lover

Reputation: 461
  • View blog
  • Posts: 3,158
  • Joined: 12-January 10

Re: MySQL CODE ERROR

Posted 23 October 2013 - 07:59 AM

what error is it throwing?
Was This Post Helpful? 0
  • +
  • -

#4 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3467
  • View blog
  • Posts: 11,768
  • Joined: 12-December 12

Re: MySQL CODE ERROR

Posted 23 October 2013 - 07:59 AM

.. in the meantime, you seem to be attempting to specify two primary keys.

It would help if you split your statement across several lines, making it easier to read.
Was This Post Helpful? 0
  • +
  • -

#5 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3719
  • View blog
  • Posts: 5,990
  • Joined: 08-June 10

Re: MySQL CODE ERROR

Posted 23 October 2013 - 08:04 AM

Don't put your entire SQL query in a single line. It's horrible to read like that. Just like any other language, you should format it properly. Makes it far easier to read and debug.

This is your query, formatted properly:
CREATE TABLE Courses( 
    CourseID INTEGER PRIMARY KEY, 
    Title VARCHAR(50)  NOT  NULL, 
    CourseLeader VARCHAR(15) NOT NULL, 
    Primary Key, 
    FOREIGN KEY (Name) 
        REFERENCES Staff (USERNAME) 
        ON DELETE SET NULL
));


You should immedately spot at least one problem. (Hint: It's on line 5.)

Also, you seem to be trying to set a FK on a field called Name, which you don't define.
Was This Post Helpful? 0
  • +
  • -

#6 java_11  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 28
  • Joined: 09-July 13

Re: MySQL CODE ERROR

Posted 24 October 2013 - 06:41 AM

Its not letting me create the table.

I have been trying for an hour now, can someone help me.
Was This Post Helpful? 0
  • +
  • -

#7 DarenR  Icon User is offline

  • D.I.C Lover

Reputation: 461
  • View blog
  • Posts: 3,158
  • Joined: 12-January 10

Re: MySQL CODE ERROR

Posted 24 October 2013 - 07:02 AM

well you have to post your errors

saying

Quote

it's broken help


doesnt tell us anything

This post has been edited by andrewsw: 24 October 2013 - 07:06 AM

Was This Post Helpful? 0
  • +
  • -

#8 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5826
  • View blog
  • Posts: 12,678
  • Joined: 16-October 07

Re: MySQL CODE ERROR

Posted 24 October 2013 - 07:08 AM

CREATE  TABLE  Courses(
   CourseID INTEGER PRIMARY KEY,
   Title VARCHAR(50)  NOT  NULL, 
   CourseLeader VARCHAR(15) NOT NULL, 
   Primary Key, -- what, exactly, is the primary key?!?  This just looks like extra
   FOREIGN KEY (Name) -- the column name "Name" isn't defined in this table.  So, huh?
      REFERENCES Staff (USERNAME) -- this seems fine
      ON DELETE SET NULL) -- looks like you have an extra ), a danger of never using a new line
);


Was This Post Helpful? 0
  • +
  • -

Page 1 of 1