--
-- SQL to drop all our tables
--
IF OBJECT_ID('GlucoseReading') IS NOT NULL
	DROP TABLE GlucoseReading
GO

IF OBJECT_ID('DiabeticAccount') IS NOT NULL
	DROP TABLE DiabeticAccount
GO

IF OBJECT_ID('DoctorAccount') IS NOT NULL
	DROP TABLE DoctorAccount
GO

IF OBJECT_ID('Diabetic') IS NOT NULL
	ALTER TABLE Diabetic
		DROP doctorID
	DROP TABLE Diabetic
GO

IF OBJECT_ID('Doctor') IS NOT NULL
	ALTER TABLE Doctor
		DROP diabeticID
	DROP TABLE Doctor
GO

--
-- SQL to create our tables.
--
Create Table Diabetic (
	diabeticID INT IDENTITY PRIMARY KEY,
	name VARCHAR(100) NOT NULL,
	diabetesType BIT	
)

Create Table DiabeticAccount (
	diabeticAccountID INT IDENTITY PRIMARY KEY,
	diabeticID INT REFERENCES Diabetic NOT NULL,
	userName VARCHAR(16) NOT NULL,
	password VARCHAR(20) NOT NULL,
	email VARCHAR(50) NOT NULL,
	lastIPAddress VARCHAR(11) NOT NULL,
	lastActive DATETIME NOT NULL
)
	
Create Table GlucoseReading (
	glucoseReadingID INT IDENTITY PRIMARY KEY,
	diabeticID INT REFERENCES Diabetic NOT NULL,
	glucoseResult FLOAT NOT NULL,
	glucoseDateTime DATETIME NOT NULL,  
	meal BIT,
	mealEntry VARCHAR(MAX),
	exercise VARCHAR(MAX),
	notes VARCHAR(MAX),
	doctorNotes VARCHAR(MAX)

	UNIQUE (glucoseDateTime)
)

Create Table Doctor	(
	doctorID INT IDENTITY PRIMARY KEY,
	diabeticID INT REFERENCES Diabetic,
	name VARCHAR(100) NOT NULL,
)

Create Table DoctorAccount (
	doctorAccountID INT IDENTITY PRIMARY KEY,
	doctorID INT REFERENCES Doctor NOT NULL,
	userName VARCHAR(16) NOT NULL,
	password VARCHAR(20) NOT NULL,
	email VARCHAR(50) NOT NULL,
	lastIPAddress CHAR(11) NOT NULL,
	lastActive DATETIME NOT NULL
)

--
-- SQL to add a column to tables
ALTER TABLE Diabetic
	ADD doctorID INT REFERENCES Doctor;
	

quote:

---

Originally posted by kenshinofkin

Sorry again but still having problems. I tried dropping the foreign key like this:

ALTER TABLE Diabetic
DROP CONSTRAINT doctorID

But I get doctorID is not a constraint??? I also left out CONSTRAINT and still no go.

---

SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE WHERE TABLE_NAME='Diabetic' AND COLUMN_NAME='doctorID'

and

SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE WHERE TABLE_NAME='Doctor' AND COLUMN_NAME='diabeticID'