I am trying to create this table in SQL Server 2008

Question

CREATE TABLE DEPARTMENTS
(Department_Id varchar(5) Primary Key NOT NULL,
 Department_Name char(20) NOT NULL,
 Manager_Id varchar(5) FOREIGN KEY REFRENCES EMPLOYEES,
 Location_Id FOREIGN KEY REFRENCES LOCATIONS)
GO

CREATE TABLE EMPLOYEES
(Employee_Id varchar Primary Key NOT NULL,
 First_Name char(20) NOT NULL,
 Last_Name char(20) NOT NULL,
 Email varchar(60) NULL,
 PhoneNumber varchar(13) NULL,
 Hire_Date date NOT NULL,
 Manager_ID varchar(5) FOREIGN KEY REFRENCES EMPLOYEES
 Department_IdFK REFRENCES DEPARTMENTS)
GO

CREATE TABLE LOCATIONS
(Location_ID varchar(5) Primary Key,
 Postal_Code no(5),
 City text(60),
 State_Province text(20),
 Country text(60))

Answer 1

You misspelled REFERENCES as REFRENCES multiple times. Try spelling it correctly to see if the problem goes away.

Answer 2

I'd leave the constraints off, but here you go.

CREATE TABLE DEPARTMENTS (
Department_Id varchar(5) Primary Key,
Department_Name char(20) NOT NULL,
Manager_Id varchar(5),
Location_Id varchar(5)
) 
GO

CREATE TABLE EMPLOYEES (
Employee_Id varchar(5) Primary Key,
First_Name char(20) NOT NULL,
Last_Name char(20) NOT NULL,
Email varchar(60) NULL,
PhoneNumber varchar(13) NULL, 
Hire_Date date NOT NULL,
Manager_ID varchar(5),
Department_Id varchar(5)
)
GO

CREATE TABLE LOCATIONS (
Location_ID varchar(5) Primary Key,
Postal_Code varchar(5),
City varchar(60),
State_Province varchar(20),
Country varchar(60)
)
GO

ALTER TABLE DEPARTMENTS WITH CHECK ADD CONSTRAINT [FK_DepartmentManager] FOREIGN KEY([Manager_Id])
REFERENCES EMPLOYEES ([Employee_Id])
GO

ALTER TABLE DEPARTMENTS WITH CHECK ADD CONSTRAINT [FK_UserLocation] FOREIGN KEY([Location_Id])
REFERENCES LOCATIONS ([Location_Id])
GO

ALTER TABLE EMPLOYEES WITH CHECK ADD CONSTRAINT [FK_EmployeeManager] FOREIGN KEY([Manager_Id])
REFERENCES EMPLOYEES ([Employee_Id])
GO

ALTER TABLE EMPLOYEES WITH CHECK ADD CONSTRAINT [FK_EmployeeDepartment] FOREIGN KEY([Department_Id])
REFERENCES DEPARTMENTS ([Department_Id])
GO

Answer 3

You didn't mention what database this is intended for, and you have some really odd data types in there....

Some general comments though (which apply mostly to SQL Server):

• in general, short strings (under 10 chars) can and should be made CHAR(x) - so your Department_Id really should be a char(5) field

• CHAR(X) fields are (at least in SQL Server) always padded to their full length with spaces - so if you have Department_Id CHAR(5) and you enter 'ABC', the column will contain 'ABC..'

• therefore, columns like first name and last name should definitely not be CHAR(20) - if you put 'Bruce' into first_name, you'll end up with 'Bruce...............' - good luck trying to find anything on such a column! Use VARCHAR(20) instead!

• if you're really using SQL Server 2005 and up, you should stop using TEXT - that datatype has been deprecated. Use VARCHAR(MAX) instead - it has all the benefits of TEXT, plus you can use all the usual string functions on it.

• my personal recommendation would also be to use INT for things like Location_Id - typically, joining and searching on an INT is faster than a VARCHAR(5) - plus with the INT, you never have to worry about misspelling it, having the wrong collation, or uppercase/lowercase issues. If you use a non-descriptive surrogate column for your ID's, which I think is a good thing, I'd make it an INT - don't use strings for IDs - it tends to get messy (except in places where the strings are used all over the place - like the state abbreviations and so forth)

Answer 4

DEPARTMENTS REFERENCES EMPLOYEES
EMPLOYEES REFERENCES EMPLOYEES

This makes these tables difficult to work with. It seems you are allowing these to be NULLable in the knowledge that to be able to create two entities in two tables you need two INSERTs plus two UPDATEs.

Also, it seems to allow an employee's manager to be different from her department's manager -- is this intended?

I would 'design out' the need for NULLable referenced/referencing columns and create two further relationship tables:

DepartmentalStaff REFERENCES Employees REFERENCES Departments
DepartmentalManagers REFERENCES DepartmentalStaff