help needed!!! - 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

SQL Server 2008 Forums

SQL Server Administration (2008)

help needed!!!

Author

Topic

banks2140
Starting Member

1 Post

Posted - 2013-11-20 : 16:12:01

I am creating a gradebook database and i need help with this code and errors im having. Here is the Code:

CREATE TABLE enrollsIn (
SID integer(30) not null,
ANo integer (4) not null,
Atype varchar(15),
SeID varchar(10),
CNum integer (4),
Sem varchar (15),
year integer(4),
LG char(2),
NG decimal (4),
primary key (SID),
#foreign key (ANo) references assignment (AssignmentNumber),
foreign key (Atype) references assignment(AssignmentType),
foreign key (SeID) references section (SecID),
foreign key (CNum) references section (CourseNum)
#foreign key (Sem) references section (Semester)
#foreign key (year) references section (Year)

);

Im getting "cannot add foreign key contraint for the one's that have the pound sign in front. when running this statement i do not include pound signs. the ones with out pound signs are the ones that work.

Here is the rest of the program:

# # gradebookschema.sql file
#
DROP DATABASE IF EXISTS gradebook;
CREATE DATABASE gradebook;
USE gradebook;

# # is used for comment in MySQL
#DROP TABLE student;
CREATE TABLE student (
FirstName varchar (15),
LastName varchar (15),
StudentID integer (30) not null,
Major char (30),
Classification varchar (10),
primary key (StudentID)
#foreign key(null)
);

#DROP TABLE course; #CASCADE CONSTRAINTS
CREATE TABLE course (
Department varchar(25),
CourseNumber integer(10) not null,
CourseName varchar(30) not null,
primary key (CourseNumber)

);

#DROP TABLE assignment; #CASCADE CONSTRAINTS;
CREATE TABLE assignment (
PointsEarned integer (4),
AssignmentType varchar(15),
AssignmentNumber integer (4) not null,
PointsPossible integer (4),
primary key (AssignmentType, AssignmentNumber)
);

#DROP TABLE section; #CASCADE CONSTRAINTS;
CREATE TABLE section (
SecID varchar(10),
CourseNum integer(10),
Semester varchar(15),
Year integer(4),
Instructor varchar(30),
primary key (SecID, CourseNum, Semester, Year),
#unique (CourseNum),
foreign key (CourseNum) references course(CourseNumber)
);

#DROP TABLE enrollsIn; #CASCADE CONSTRAINTS;
CREATE TABLE enrollsIn (
SID integer(30) not null,
ANo integer (4) not null,
Atype varchar(15),
SeID varchar(10),
CNum integer (4),
Sem varchar (15),
year integer(4),
LG char(2),
NG decimal (4),
primary key (SID),
#foreign key (ANo) references assignment (AssignmentNumber),
foreign key (Atype) references assignment(AssignmentType),
foreign key (SeID) references section (SecID),
foreign key (CNum) references section (CourseNum)
#foreign key (Sem) references section (Semester)
#foreign key (year) references section (Year)

);

PLEASE HELP

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-21 : 01:11:19

Are you looking for MySQL conde? if yes, you may be better off posting this in some MySQL forums.
If in t-sql. the foreignkeys should be defined as

ANo integer (4) not null REFERENCES assignment (AssignmentNumber),
...

Simlarly PRIMARY KEY as

CourseNumber integer(10) not null PRIMARY KEY

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources