May 15, 2008 at 9:42 pm
Hey.
I'm trying to create some tables in my database but I'm getting some errors... The one which is causing the most trouble is Msg 1767, Level 16, State 0, Line 38
Foreign key 'ten_fk' references invalid table 'Tenant'.
I'm not sure why it's complaining... can anyone help me out here? I've also posted this on SQL Team's forums but haven't got a reply yet.
Cheers!
-- 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;
Errors:
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.
May 15, 2008 at 9:57 pm
Looks like rights, you can't drop those tables.
Need to get an admin to give you rights.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
May 15, 2008 at 10:05 pm
you are trying to create to foreign key reference to the tenant table before the tenant table has been created. All you need to do is move the creation of the tenant table to before the foreign key reference and you should be ok
May 15, 2008 at 10:33 pm
Steve Jones - Editor (5/15/2008)
Looks like rights, you can't drop those tables.Need to get an admin to give you rights.
I don't know how that could be though, because I'm the only account on this computer and it's an admin account. That's what you mean, right? I've been able to drop other tables in the past.
happycat59 (5/15/2008)
you are trying to create to foreign key reference to the tenant table before the tenant table has been created. All you need to do is move the creation of the tenant table to before the foreign key reference and you should be ok
But Tenant references Property. If I moved Tenant further up, then it will complain about not being able to find Property, won't it?
Cheers for your help so far. 🙂
May 16, 2008 at 12:16 am
You are trying to create the foreign key before you create the table. check that...
May 16, 2008 at 1:55 am
Yeah. I didn't get the table creation order right.
Cheers.
May 16, 2008 at 8:29 am
The permissions might be that the tables aren't there. I was thinking you were dropping the table to add the FK. Typically you do an "IF Exists" before issuing the drop to prevent errors.
Both tables must exists before the FK can be created. I think part of the issue might be that you're putting this in one batch. I put all tables in separate batches, really separate files so that I can use version control for the schema. You want to know what you did later.
You can add the FK later, in a separate batch, after the table exists.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
May 16, 2008 at 9:13 pm
Steve Jones - Editor (5/16/2008)
The permissions might be that the tables aren't there. I was thinking you were dropping the table to add the FK. Typically you do an "IF Exists" before issuing the drop to prevent errors.Both tables must exists before the FK can be created. I think part of the issue might be that you're putting this in one batch. I put all tables in separate batches, really separate files so that I can use version control for the schema. You want to know what you did later.
You can add the FK later, in a separate batch, after the table exists.
Unfortunately I have to have it all in one file... :pinch:Otherwise I'd do it your way.
Edit: This problem has been solved.
June 15, 2010 at 9:18 am
i had the same problem. Try to check what database have you selected. In my case i was using ´master´ LOL.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply