Why won't this work?! - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Why won't this work?!

Author

Topic

archon
Starting Member

6 Posts

Posted - 2009-10-22 : 02:25:49

Ok, I'm using SQL for the first time. I have an assignment due tomorrow, and there is one line of Code which is giving me a hard time.I'll post all my code and then show you the line where it fails.

-- COMP 3380 (Fall 2009)

CREATE TABLE Students(sID INTEGER NOT NULL PRIMARY KEY,sName VARCHAR(20))
CREATE TABLE Courses(cID VARCHAR(20) NOT NULL PRIMARY KEY, cName VARCHAR(20))
CREATE TABLE Sect(cID VARCHAR(20) NOT NULL,sectID VARCHAR(20) NOT NULL,profName VARCHAR(20),PRIMARY KEY(cID,sectID), FOREIGN KEY(cID) REFERENCES Courses ON DELETE CASCADE)
CREATE TABLE Enrolled(sID INTEGER NOT NULL , sectID VARCHAR(20) NOT NULL, cID VARCHAR(20) NOT NULL, grade VARCHAR(20),  PRIMARY KEY(sID,sectID,cID), FOREIGN KEY(sID) REFERENCES Students, FOREIGN KEY(cID) REFERENCES Courses, FOREIGN KEY(sectID) REFERENCES Sect)



CREATE USER SA PASSWORD "" ADMIN

INSERT INTO Students VALUES('6700001','Albert')
INSERT INTO Students VALUES('6700002',‘Bob’)
INSERT INTO Courses VALUES(‘COMP 3380’,‘DB 1’)
INSERT INTO Sect VALUES(‘COMP 3380’,’A01’,‘Dr. Leung’)
INSERT INTO Enrolled VALUES('6700001',‘A01’,‘COMP 3380’,‘A+’)
INSERT INTO Enrolled VALUES('6700002',‘A01’,‘COMP 3380’,’A+’)

INSERT INTO Students VALUES('6700003',’Chris’)
INSERT INTO Coures VALUES(‘COMP 4380’,’DB 2’)
INSERT INTO Courses VALUES(‘COMP 4710’,‘Data Mining’)
INSERT INTO Sect VALUES(‘COMP 4380’,‘A01’,‘Dr. Leung’)
INSERT INTO Section VALUES(‘COMP 4710’,‘A01’,‘Dr. Leung’)
INSERT INTO Enrolled VALUES('6700003',‘A01’,’COMP 4380’,‘B+’)
INSERT INTO Enrolled VALUES('6700003',’A01’,’COMP 4710’,’B+’)

INSERT INTO Students VALUES('6700004',’Don’)
INSERT INTO Courses VALUES(‘COMP 4060’,’Topics in CS’)
INSERT INTO Sect VALUES(‘COMP 4060’,’T01’,"Null")
INSERT INTO Sect VALUES(‘COMP 4060’,’T04’,"Null")
INSERT INTO Enrolled VALUES('6700004',’T01’,’COMP 4060’,’A’)
INSERT INTO Enrolled VALUES('6700004',’T04’,’COMP 4060’,’A’)

The 4th line with create, the one in bold seems to be the problem. It says the coloum count doesn't match the statement. Can someone help translate that? This is due soon! Thanks for your help.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2009-10-22 : 06:41:51

What column on the students table does sID meant ot be a foreign-key to?...and other tables as well.

you've left out something in the FK clause.

X002548
Not Just a Number

15586 Posts

Posted - 2009-10-22 : 10:13:33

How long did they give for the assignment?

Anyway

You need a GO after the table creates

And take out

CREATE USER SA PASSWORD "" ADMIN

Also Add Data

INSERT INTO Students VALUES('xxx','archon')
--INSERT INTO Courses VALUES(‘COMP 3380’,‘DB 1’)
--INSERT INTO Sect VALUES(‘COMP 3380’,’A01’,‘Dr. Leung’)
INSERT INTO Enrolled VALUES('xxx',‘A01’,‘COMP 3380’,‘C at Best’)

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

archon
Starting Member

6 Posts

Posted - 2009-10-22 : 11:21:00

Thanks for your help AndrewMurphy. I looked it up, and added the coloumn title of the referenced Table to the FK statement. That allowed me to get as far as another error.

Primary or Unique constraint required on main table: Section in Statement: CREATE TABLE Enrolled...

I'm not sure how to rectify this, because all the foreign keys in Enrolled ARE Primary keys in their own tables, including Section.

archon
Starting Member

6 Posts

Posted - 2009-10-22 : 11:25:37

Very funny X002548, but not all that helpful. I'm merely editing a file given to us by our instructor, who said nothing about adding GO or removing CREATE USER SA PASSWORD "" ADMIN.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2009-10-22 : 11:48:47

As an fyi...

When a member has "11636+ Posts" his advice is usually a) useful & b) appreciated.
Your timeframe conundrum has no upside or downside for us. After all our advice was given freely.
Being sarcastic downgrades the chances ANYbody will help you.

Go back to the instructor and ask for advice/clarification. It's allowed.
Or re-read the course notes or ask fellow students.

archon
Starting Member

6 Posts

Posted - 2009-10-22 : 12:04:17

His advice on the Go and removing that one line of code may be very good advice. It's advice that I can't follow because I'm only supposed to edit certain parts of the file given to me. However it's clear that the rest of his advice was a joke at my expense. Please correct me if I'm wrong. I'm not trying to be sarcastic, I appreciate any help I get. You, AndrewMurphy have been very helpful, thank you. I don't appreciate unecessary jokes, especially when I'm in a bad mood. I'm a very nice and polite person actually, I only expect the same from others.

archon
Starting Member

6 Posts

Posted - 2009-10-22 : 12:24:10

After fiddling around with it for a while I finally got it to work. I'll post the working code so that if anyone else has this problem they can see what I did to fix it.

-- COMP 3380 (Fall 2009)

CREATE TABLE Students(sID INTEGER NOT NULL PRIMARY KEY,sName VARCHAR(20))
CREATE TABLE Courses(cID VARCHAR(20) NOT NULL PRIMARY KEY, cName VARCHAR(20))
CREATE TABLE Section(cID VARCHAR(20) NOT NULL ,sectID VARCHAR(20) NOT NULL ,profName VARCHAR(20),PRIMARY KEY(cID,sectID), FOREIGN KEY(cID) REFERENCES Courses (cID) ON DELETE CASCADE)
CREATE TABLE Enrolled(sID INTEGER NOT NULL , sectID VARCHAR(20) NOT NULL, cID VARCHAR(20) NOT NULL, grade VARCHAR(20), PRIMARY KEY(sID,sectID,cID), FOREIGN KEY(sID) REFERENCES Students (sID), FOREIGN KEY(cID,sectID) REFERENCES Section(cID,sectID))

CREATE USER SA PASSWORD "" ADMIN

INSERT INTO Students VALUES(6700001,'Albert')
INSERT INTO Students VALUES(6700002,'Bob')
INSERT INTO Courses VALUES('COMP 3380','DB 1')
INSERT INTO Section VALUES('COMP 3380','A01','Dr. Leung')
INSERT INTO Enrolled VALUES(6700001,'A01','COMP 3380','A+')
INSERT INTO Enrolled VALUES(6700002,'A01','COMP 3380','A+')

INSERT INTO Students VALUES(6700003,'Chris')
INSERT INTO Courses VALUES('COMP 4380','DB 2')
INSERT INTO Courses VALUES('COMP 4710','Data Mining')
INSERT INTO Section VALUES('COMP 4380','A01','Dr. Leung')
INSERT INTO Section VALUES('COMP 4710','A01','Dr. Leung')
INSERT INTO Enrolled VALUES(6700003,'A01','COMP 4380','B+')
INSERT INTO Enrolled VALUES(6700003,'A01','COMP 4710','B+')

INSERT INTO Students VALUES(6700004,'Don')
INSERT INTO Courses VALUES('COMP 4060','Topics in CS')
INSERT INTO Section VALUES('COMP 4060','T01',"Null")
INSERT INTO Section VALUES('COMP 4060','T04',"Null")
INSERT INTO Enrolled VALUES(6700004,'T01','COMP 4060','A')
INSERT INTO Enrolled VALUES(6700004,'T04','COMP 4060','A')

X002548
Not Just a Number

15586 Posts

Posted - 2009-10-22 : 12:55:19

hey...well at least you caught it

I still stanf by it because of your "emergency"

But we are still here to help

I did some research and it seems like you are using SQL Server Express...which I have to admit very limited usage of

This must be lesson #1

I wouldn't mix Admin stuff (CREATE USER SA PASSWORD "" ADMIN) and app developement (your tables and such) in the same file

I would have had an exercide devoted to Admin setup

In ANY case

If the instructor just wanted you to cut and past the script and it should just run as is, then you should be able to tell him/her....hey it's not working

right?

How can we serve you?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

archon
Starting Member

6 Posts

Posted - 2009-10-22 : 14:11:25

I know that from my 'emergency' I would appear to be a procrastinator. However what I didn't mention is that I had 4 assignments due last week, and this week I had 2 midterms,a quiz and a project proposal to work on as well as this assignment. I'm extremely busy, I swear my professors are conspiring against me.

Thanks for your help, I'll keep your advice in mind. I don't require any more help, everything is working fine.

X002548
Not Just a Number

15586 Posts

Posted - 2009-10-22 : 18:26:20

Did you sign up for a time manglement course?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

X002548
Not Just a Number

15586 Posts

Posted - 2009-10-22 : 18:28:28

quote:
Originally posted by archon

I know that from my 'emergency' I would appear to be a procrastinator.

No, no...I know how it goes...the ugliest task gets routed to the bottom

This, however, would have been on the top of my que

ummmmm...wait...that would be pub first, the computer lab

Hell you probably don't even NEED to go to a lab these days, and just work pon the laptop in the pub!

Am I right...or am I right?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-10-22 : 18:41:14

90 days to write that term paper but you had no time!

Noah could've gone thru two floods and still have had 10 days to write that paper!

Jim

N.B. This post made for entertainment purposes only. Any resemblance to
any persons real or imagined are purely coincidental. Don't try this at home. Your results may vary.

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources