Rookie question - 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

Rookie question

Author

Topic

joshua_k

2 Posts

Posted - 2011-04-15 : 11:37:38

I'm trying to put together a simple database to store information for a preschool. I'm new to SQL and happy that I got 50% of my tables right, but obviously 50% won't cut it. I highlighted in red the error messages I get.
Thanks in advance for any help.

----------------------------------
--Create table to store Primary Contact information
----------------------------------
CREATE TABLE Primary_Contact
(
primaryContactID INT IDENTITY(1,1),
primaryContactLast VARCHAR(40) NOT NULL,
primaryContactFirst VARCHAR(40) NOT NULL,
primaryContactCell VARCHAR(20) NOT NULL,
primaryContactEmail VARCHAR(50) NOT NULL,
primaryContactStreet VARCHAR(30),
primaryContactTown VARCHAR(30),
primaryContactState VARCHAR(30),
primaryContactZip VARCHAR(10),
primaryContactNotes VARCHAR(30),
CONSTRAINT Primary_Contact_PK PRIMARY KEY (primaryContactID)
);
----------------------------------
--Create table to store Pediatrician information
----------------------------------
CREATE TABLE Pediatrician
(
pediatricianID INT IDENTITY(1,1),
pediatricianLast VARCHAR(40) NOT NULL,
pediatricianFirst VARCHAR(40) NOT NULL,
pediatricianWorkPhone VARCHAR(20) NOT NULL,
pediatricianCellPhone VARCHAR(20) NOT NULL,
pediatricianEmail VARCHAR(50) NOT NULL,
pediatricianStreet VARCHAR(30),
pediatricianTown VARCHAR(30),
pediatricianState VARCHAR(30),
pediatricianZip VARCHAR(10),
pediatricianNotes VARCHAR(30),
CONSTRAINT Pediatrician_PK PRIMARY KEY (pediatricianID)
);
----------------------------------
--Create table to store Staff information
----------------------------------
CREATE TABLE Staff
(
staffID INT IDENTITY(1,1),
staffLast VARCHAR(40) NOT NULL,
staffFirst VARCHAR(40) NOT NULL,
staffHomePhone VARCHAR(20) NOT NULL,
staffCell VARCHAR(20) NOT NULL,
staffEmail VARCHAR(50) NOT NULL,
staffStreet VARCHAR(30),
staffTown VARCHAR(30),
staffState VARCHAR(30),
staffZip VARCHAR(10),
CONSTRAINT Staff_PK PRIMARY KEY (staffID)
);
----------------------------------
--Create table to store Class information
----------------------------------
CREATE TABLE Class
(
classID INT IDENTITY(1,1),
classTypeID INT,
classDate VARCHAR(10) NOT NULL,
classTime VARCHAR(50) NOT NULL,
classCost DECIMAL(10,2) NOT NULL,
CONSTRAINT Class_PK PRIMARY KEY (classID),
CONSTRAINT Class_PK PRIMARY KEY (classTypeID),
CONSTRAINT Class_FK1 FOREIGN KEY (classTypeID)
);

Msg 102, Level 15, State 1, Line 11
Incorrect syntax near ')'.

----------------------------------
--Create table to store Class Type information
----------------------------------
CREATE TABLE Class_Type
(
classTypeID INT IDENTITY(1,1),
classTypeName VARCHAR(40) NOT NULL,
classTypeDesc VARCHAR(40) NOT NULL,
CONSTRAINT Class_Type_PK PRIMARY KEY (classTypeID)
);
select * from Class_Type
----------------------------------
--Create table to store Class Leaders information
----------------------------------
CREATE TABLE Class_Leaders
(
staffID INT,
classID INT,
classTypeID INT,
CONSTRAINT Staff_PK PRIMARY KEY (staffID),
CONSTRAINT Staff_FK1 FOREIGN KEY (staffID)
REFERENCES Staff (staffID),
CONSTRAINT Class_PK PRIMARY KEY (classID),
CONSTRAINT Class_FK2 FOREIGN KEY (classID)
REFERENCES Class (classID),
CONSTRAINT Class_Type_PK PRIMARY KEY (classTypeID),
CONSTRAINT Class_Type_FK2 FOREIGN KEY (classTypeID)
REFERENCES Class (classID),
);

Msg 8110, Level 16, State 0, Line 1
Cannot add multiple PRIMARY KEY constraints to table 'Class_Leaders'.
Msg 8110, Level 16, State 0, Line 1
Cannot add multiple PRIMARY KEY constraints to table 'Class_Leaders'.

----------------------------------
--Create table to store Student Registration information
----------------------------------
CREATE TABLE Student_Registration
(
registrationID INT IDENTITY(1,1),
classID INT,
classTypeID INT,
studentID INT,
CONSTRAINT Student_FK1 FOREIGN KEY (studentID)
REFERENCES Student (studentID),
CONSTRAINT Class_FK2 FOREIGN KEY (classID)
REFERENCES Class (classID),
CONSTRAINT Class_Type_FK2 FOREIGN KEY (classTypeID)
REFERENCES Class (classID),
);

Msg 1776, Level 16, State 0, Line 1
There are no primary or candidate keys in the referenced table 'Student' that match the referencing column list in the foreign key 'Student_FK1'.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.

----------------------------------
--Create table to store Student information
----------------------------------
CREATE TABLE Student
(
studentID INT IDENTITY(1,1),
pediatricianID INT,
studentLast VARCHAR(40) NOT NULL,
studentFirst VARCHAR(40) NOT NULL,
studentBirthDate VARCHAR(20) NOT NULL,
studentReligion VARCHAR(20),
studentAllergies VARCHAR(50),
studentMedicalConditions VARCHAR(50),
CONSTRAINT Pediatrician_FK1 FOREIGN KEY (pediatricianID)
REFERENCES Pediatrician (pediatricianID),
);
----------------------------------
--Create table to store Contact information
----------------------------------
CREATE TABLE Contact
(
primaryContactID INT,
studentID INT,
primaryContactRole VARCHAR(20),
CONSTRAINT Primary_Contact_PK PRIMARY KEY (primaryContactID),
CONSTRAINT Primary_Contact_FK1 FOREIGN KEY (primaryContactID)
REFERENCES Primary_Contact (primaryContactID),
CONSTRAINT Student_PK PRIMARY KEY (studentID),
CONSTRAINT Student_FK2 FOREIGN KEY (studentID)
REFERENCES Class (studentID),
);

Msg 8110, Level 16, State 0, Line 1
Cannot add multiple PRIMARY KEY constraints to table 'Contact'.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-04-15 : 12:00:55

CONSTRAINT Class_FK1 FOREIGN KEY (classTypeID) reference tbl (col)

Cannot add multiple PRIMARY KEY constraints to table 'Class_Leaders'.
exactly what it says

There are no primary or candidate keys in the referenced table 'Student' that match the referencing column list in the foreign
Need to create student first

Cannot add multiple PRIMARY KEY constraints to table 'Contact'.
as above

The extra primary keys should be unique indexes

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

joshua_k

2 Posts

Posted - 2011-04-15 : 17:59:07

Cool..Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources