August 12, 2008 at 4:57 am
Hey guys , Im new to SQL and was wondering if you could shed some light on this problem im having..
'Historical_propertys' table saved successfully
'Property_Managers' table
- Unable to create relationship 'FK_Property_Managers_Historical_propertys'.
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_Property_Managers_Historical_propertys". The conflict occurred in database "e5053759", table "dbo.Historical_propertys", column 'Property_ID'.
i'm a complete newbie at SQL and specially the design aspect of it. all of the property_ID's match up with the correct property_manager_ID's , when i try and save it i get the above message , i can still add data into the table , but this is because i havent saved it , all of the other realtionships work fine , but not that one...to...one .
I've been sturggling with it for 2 days now , might be something simple to you guys but i would really appreciate it if you could help.
i have uploaded sceen prints of the data model , with an arrow pointing to the relationship.
a screen shot of both tables causing me problems.
Thanks for your time , San.
August 12, 2008 at 5:14 am
Also , i have tried putting porperty_ID as the primary key in propery managers and it works fine , is this acceptable for me to have property_ID as a primary key in both tables? and does it look as if it should be in this case?
thanks , again 🙂
August 12, 2008 at 7:28 am
I don't see anything immediately visible that would prevent you from creating that constraint. Can you post the scripts to recreate the tables? There may be a constraint of some time preventing this.
It's usually pretty clearly a data issue.
Also, it's fine to have a column be both a foreign key and part of the primary key.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 12, 2008 at 7:35 am
It looks like it is an issue with the data that is currently in the tables, are you able to clear out the tables then create the relationship?
August 13, 2008 at 4:04 am
steveb (8/12/2008)
It looks like it is an issue with the data that is currently in the tables, are you able to clear out the tables then create the relationship?
Yeah , i can create the reltionship minus the data , but the data is accurate , i must have checked it about 50 times , all propertys link up to the correct property manager ID and vice vera , it saves fine when i change the primary key in property_managers to property_ID.
Im still a bit unsure if this is wrong in anyway ? thanks for your time! san
August 13, 2008 at 6:50 am
Can you post the scripts you're using to create the tables? There may be something there.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 13, 2008 at 7:12 am
Grant Fritchey (8/13/2008)
Can you post the scripts you're using to create the tables? There may be something there.
USE [e5053759]
GO
/****** Object: Table [dbo].[Property_Managers] Script Date: 08/13/2008 14:11:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Property_Managers](
[Prop_manager_ID] [nvarchar](50) NOT NULL,
[Property_ID] [nvarchar](50) NOT NULL,
[Property_Name] [nvarchar](50) NULL,
[Forename] [nvarchar](50) NOT NULL,
[Surname] [nvarchar](50) NOT NULL,
[Telephone_Number] [nvarchar](50) NULL,
CONSTRAINT [PK_Property_Managers_1] PRIMARY KEY CLUSTERED
(
[Property_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
USE [e5053759]
GO
/****** Object: Table [dbo].[Historical_propertys] Script Date: 08/13/2008 14:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Historical_propertys](
[Property_ID] [nvarchar](50) NOT NULL,
[Property_Name] [nvarchar](50) NOT NULL,
[Prop_manager_ID] [nvarchar](50) NOT NULL,
[Admission_ID] [nvarchar](50) NOT NULL,
[Type_Property] [nvarchar](50) NOT NULL,
[Location] [nvarchar](50) NOT NULL,
[Profit_record_ID] [nvarchar](50) NOT NULL,
[Visitor_record_ID] [nvarchar](50) NOT NULL,
[Open_Times] [nvarchar](50) NOT NULL,
[Closed_Times] [nvarchar](50) NOT NULL,
[Closed_Days] [nvarchar](50) NULL,
CONSTRAINT [PK_Historical_propertys] PRIMARY KEY CLUSTERED
(
[Property_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Historical_propertys] WITH CHECK ADD CONSTRAINT [FK_Historical_propertys_Property_Managers] FOREIGN KEY([Property_ID])
REFERENCES [dbo].[Property_Managers] ([Property_ID])
GO
ALTER TABLE [dbo].[Historical_propertys] CHECK CONSTRAINT [FK_Historical_propertys_Property_Managers]
August 13, 2008 at 7:41 am
Let me understand this...
The Historical_Propertys table has a pk, Property_ID. The Property_Managers table has a pk, Property_ID. There is a foreign key constraint on the Historical_Propertys table against the Property_Managers table on the Property_ID. That means that the parent table in the relationship is the Propety_Managers table and the child is the Historical_Propertys table. Is all that true?
If so, you can't then put a foreign key constraint going the other way, making Historical_Properties the parent of Property_Managers as was suggested by the original post. That may be the problem.
If you need the Historical_Propertys table to be the parent, then you need to drop the other constraint and recheck the data.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 13, 2008 at 8:15 am
ive never really been taught about parent and child , i think like this.
one property manger manages one property.
one propert has one property manager.
i need a 1-1 relationship , i elected property_manager_ID as my primary key for the property_manager table and i elected property_ID for my historical_property table.
when i tried to do the 1.1 relationship i got that ALTER STATEMENT table.
the only way i could get around this is by electing property_ID as my primary key in the property_managers table.
If there was a problem with my data , what should i be keeping an eye out for?
all my property_ID's match up with the correct property managers in both tables.
thanks again for your time , san
August 13, 2008 at 8:32 am
This really gets into DB design, but based on what you're saying, I would drop the FK that is currently in place on the Property table. From what you say, the Property should be the driving factor, not the manager. By the way, based on what you're saying, the PropertyId is not the appropriate primary key for the manager. Yes, it may be a one-to-one relationship, but the property doesn't define the manager any more than the manager defines the property. You have the appropriate primary key on the property table, but you should probably use the ManagerId on the Manager table and then create a foreign key to the Property table.
I'm giving this advice not knowing all the business requirements, but based on what you've said, this sounds right. Because you have data in your tables, you might run into issues recreating these structures, but since you've gone down a bit of a bad path, you'll either need to adjust the data so that it's correct or toss it & reenter it. Also, please evaluate all your data types rather than using the default NVARCHAR(50). PropertyId & ManagerId, for example, should probably be integer data types unless you're using some user generated identifier.
CREATE TABLE [dbo].[Historical_propertys](
[Property_ID] [nvarchar](50) NOT NULL,
[Property_Name] [nvarchar](50) NOT NULL,
[Prop_manager_ID] [nvarchar](50) NOT NULL,
[Admission_ID] [nvarchar](50) NOT NULL,
[Type_Property] [nvarchar](50) NOT NULL,
[Location] [nvarchar](50) NOT NULL,
[Profit_record_ID] [nvarchar](50) NOT NULL,
[Visitor_record_ID] [nvarchar](50) NOT NULL,
[Open_Times] [nvarchar](50) NOT NULL,
[Closed_Times] [nvarchar](50) NOT NULL,
[Closed_Days] [nvarchar](50) NULL,
CONSTRAINT [PK_Historical_propertys] PRIMARY KEY CLUSTERED
(
[Property_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[Property_Managers](
[Prop_manager_ID] [nvarchar](50) NOT NULL,
[Property_ID] [nvarchar](50) NOT NULL,
[Property_Name] [nvarchar](50) NULL,
[Forename] [nvarchar](50) NOT NULL,
[Surname] [nvarchar](50) NOT NULL,
[Telephone_Number] [nvarchar](50) NULL,
CONSTRAINT [PK_Property_Managers_1] PRIMARY KEY CLUSTERED
(
[Prop_manager_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Property_Managers] WITH CHECK ADD CONSTRAINT [FK_Property_Managers_Historical_propertys] FOREIGN KEY([Property_ID])
REFERENCES [dbo].[Historical_propertys] ([Property_ID])
GO
ALTER TABLE [dbo].[Property_Managers] CHECK CONSTRAINT [FK_Property_Managers_Historical_propertys]
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 14, 2008 at 4:21 am
Grant Fritchey (8/13/2008)
This really gets into DB design, but based on what you're saying, I would drop the FK that is currently in place on the Property table. From what you say, the Property should be the driving factor, not the manager. By the way, based on what you're saying, the PropertyId is not the appropriate primary key for the manager. Yes, it may be a one-to-one relationship, but the property doesn't define the manager any more than the manager defines the property. You have the appropriate primary key on the property table, but you should probably use the ManagerId on the Manager table and then create a foreign key to the Property table.I'm giving this advice not knowing all the business requirements, but based on what you've said, this sounds right. Because you have data in your tables, you might run into issues recreating these structures, but since you've gone down a bit of a bad path, you'll either need to adjust the data so that it's correct or toss it & reenter it. Also, please evaluate all your data types rather than using the default NVARCHAR(50). PropertyId & ManagerId, for example, should probably be integer data types unless you're using some user generated identifier.
CREATE TABLE [dbo].[Historical_propertys](
[Property_ID] [nvarchar](50) NOT NULL,
[Property_Name] [nvarchar](50) NOT NULL,
[Prop_manager_ID] [nvarchar](50) NOT NULL,
[Admission_ID] [nvarchar](50) NOT NULL,
[Type_Property] [nvarchar](50) NOT NULL,
[Location] [nvarchar](50) NOT NULL,
[Profit_record_ID] [nvarchar](50) NOT NULL,
[Visitor_record_ID] [nvarchar](50) NOT NULL,
[Open_Times] [nvarchar](50) NOT NULL,
[Closed_Times] [nvarchar](50) NOT NULL,
[Closed_Days] [nvarchar](50) NULL,
CONSTRAINT [PK_Historical_propertys] PRIMARY KEY CLUSTERED
(
[Property_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[Property_Managers](
[Prop_manager_ID] [nvarchar](50) NOT NULL,
[Property_ID] [nvarchar](50) NOT NULL,
[Property_Name] [nvarchar](50) NULL,
[Forename] [nvarchar](50) NOT NULL,
[Surname] [nvarchar](50) NOT NULL,
[Telephone_Number] [nvarchar](50) NULL,
CONSTRAINT [PK_Property_Managers_1] PRIMARY KEY CLUSTERED
(
[Prop_manager_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Property_Managers] WITH CHECK ADD CONSTRAINT [FK_Property_Managers_Historical_propertys] FOREIGN KEY([Property_ID])
REFERENCES [dbo].[Historical_propertys] ([Property_ID])
GO
ALTER TABLE [dbo].[Property_Managers] CHECK CONSTRAINT [FK_Property_Managers_Historical_propertys]
So the code you have pasted below is what it should look like with the correct constraint in place Property_manager_ID -----> property_ID ?
Thanks again , san
August 14, 2008 at 5:35 am
If my understanding of what you're trying to do is correct, then yeah, I think that's the right code. But please, test it and verify it. Don't simply take my word for it.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 14, 2008 at 6:39 am
I executed the code and added the tables to the diagram but it gives it a one to many relationship , which contradicts what i need to put , because only one property manager only manages one property.
I also re entered new data in. If i was looking for a problem with my data , what should i look for?
i appreciate yout time , thanks , san
August 14, 2008 at 8:10 am
You can make the foreign key non-null, which will force you to have a value, and you can put a unique constraint on the foreign key, making it so only one value can be placed there.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply