SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 help needed!!!
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

banks2140
Starting Member

1 Posts

Posted - 11/20/2013 :  16:12:01  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 11/21/2013 :  01:11:19  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000