Msg 1767, Level 16, State 0, Line 38
Foreign key 'ten_fk' references invalid table 'Tenant'.

-- Mitch Curtis
-- A2create.sql

-- Set the active database to KWEA.
USE KWEA;

-- Drop existing tables (if any).
DROP TABLE Ownership;
DROP TABLE Tenant;
DROP TABLE Staff;
DROP TABLE Property;
DROP TABLE Property_Status_Report;
DROP TABLE Property_Owner;
DROP TABLE Placement_Record;
DROP TABLE Candidate_Tenant;
DROP TABLE Waiting_List;

-- Create new tables.
CREATE TABLE Waiting_List
(
	waiting# INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
	candidate_name VARCHAR(20) NOT NULL,
	anticipated_start_date SMALLDATETIME NULL,
	anticipated_end_date SMALLDATETIME NULL,
	max_affordable_rent SMALLMONEY NOT NULL
);

CREATE TABLE Candidate_Tenant
(
	candidate# INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
	waiting# INT NULL,
	name VARCHAR(20) NOT NULL,
	phone_number INT NOT NULL,
	required_property_type VARCHAR(10) NOT NULL,
	CONSTRAINT w_fk FOREIGN KEY(waiting#) REFERENCES Waiting_List(waiting#)
);

CREATE TABLE Placement_Record
(
	opening# INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
	tenant# INT NOT NULL,
	start_date SMALLDATETIME NOT NULL,
	end_date SMALLDATETIME NOT NULL,
	total_bonds SMALLMONEY NOT NULL,
	weekly_rent SMALLMONEY NOT NULL,
	CONSTRAINT ten_fk FOREIGN KEY(tenant#) REFERENCES Tenant(tenant#)
);

CREATE TABLE Property_Owner
(
	owner# INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
	name VARCHAR(20) NOT NULL,
	phone_number INT NOT NULL
);

CREATE TABLE Property_Status_Report
(
	address VARCHAR(30) NOT NULL,
	report_date SMALLDATETIME NOT NULL,
	weekly_rent SMALLMONEY NOT NULL,
	month_rent_start_date SMALLDATETIME NOT NULL,
	month_rent_end_date SMALLDATETIME NOT NULL,
	maintenance_fee SMALLMONEY NOT NULL,
	month_inspection_history VARCHAR(30) NULL,
	CONSTRAINT ar_pk PRIMARY KEY(address, report_date),
	FOREIGN KEY(address) REFERENCES Property(address)
);

CREATE TABLE Property
(
	address VARCHAR(30) PRIMARY KEY NOT NULL,
	staff# INT IDENTITY(1,1) NOT NULL,
	type VARCHAR NOT NULL,
	occupant_limit INT NOT NULL,
	comments VARCHAR(30) NULL,
	FOREIGN KEY(staff#) REFERENCES Staff(staff#)
);

CREATE TABLE Staff
(
	staff# INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
	manager# INT NOT NULL,
	name VARCHAR(20) NOT NULL,
	FOREIGN KEY(manager#) REFERENCES Staff(staff#)
);

CREATE TABLE Tenant
(
	tenant# INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
	staff# INT NOT NULL,
	property_address VARCHAR(30) NOT NULL,
	name VARCHAR(20) NOT NULL,
	phone_number INT NOT NULL,
	street VARCHAR(20) NOT NULL,
	city VARCHAR(20) NOT NULL,
	postcode INT NOT NULL,
	category VARCHAR(10) NOT NULL,
	comments VARCHAR(30) NULL,
	FOREIGN KEY(staff#) REFERENCES Staff(staff#),
	FOREIGN KEY(property_address) REFERENCES Property(address)
);

CREATE TABLE Ownership
(
	address VARCHAR(30) NOT NULL,
	owner# INT NOT NULL,
	CONSTRAINT ao_pk PRIMARY KEY(address, owner#),
	FOREIGN KEY(address) REFERENCES Property(address),
	FOREIGN KEY(owner#) REFERENCES Property_Owner(owner#)
);

-- Display tables.
SELECT * FROM Waiting_List;
SELECT * FROM Candidate_Tenant;
SELECT * FROM Placement_Record;
SELECT * FROM Property_Owner;
SELECT * FROM Property_Status_Report;
SELECT * FROM Property;
SELECT * FROM Staff;
SELECT * FROM Tenant;
SELECT * FROM Ownership;

Msg 3701, Level 11, State 5, Line 8
Cannot drop the table 'Ownership', because it does not exist or you do not have permission.
Msg 3701, Level 11, State 5, Line 9
Cannot drop the table 'Tenant', because it does not exist or you do not have permission.
Msg 3701, Level 11, State 5, Line 10
Cannot drop the table 'Staff', because it does not exist or you do not have permission.
Msg 3701, Level 11, State 5, Line 11
Cannot drop the table 'Property', because it does not exist or you do not have permission.
Msg 3701, Level 11, State 5, Line 12
Cannot drop the table 'Property_Status_Report', because it does not exist or you do not have permission.
Msg 3701, Level 11, State 5, Line 13
Cannot drop the table 'Property_Owner', because it does not exist or you do not have permission.
Msg 3701, Level 11, State 5, Line 14
Cannot drop the table 'Placement_Record', because it does not exist or you do not have permission.
Msg 1767, Level 16, State 0, Line 38
Foreign key 'ten_fk' references invalid table 'Tenant'.
Msg 1750, Level 16, State 0, Line 38
Could not create constraint. See previous errors.

if object_id('YourTable') is not null
begin
     drop table YourTable
end