Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Primary-Foreign Key Expand / Collapse
Author
Message
Posted Tuesday, May 25, 2010 3:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 25, 2010 3:26 AM
Points: 7, Visits: 21
Hai,

There are 2 tables. Table 1 contains col1 as primary key and col2 as foreign key refers to Table 2 - col1. The same way Table 2 contains col1 as primary key and col2 refers to Table 1 - col 1.

Can anyone tell me how can we insert values in Table 2?


Thanks & Regards,
Krish.
(Together We WIN)
Post #927314
Posted Tuesday, May 25, 2010 4:32 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 6:19 AM
Points: 3,917, Visits: 5,109
There is no way to do this simply.
This is a bad design.
To enable this, you will have to drop the foreign keys, do the insert (s) then recreate the foreign keys WITH(CHECK)


____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Post #927353
Posted Tuesday, May 25, 2010 6:06 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 3:34 PM
Points: 15,661, Visits: 28,051
That's a completely circular reference. While you can set those up, they're impossible to maintain.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #927390
Posted Thursday, May 27, 2010 6:13 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 9:41 AM
Points: 3,241, Visits: 4,999
Its wrong design but Why not?


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Table1](
[PKID] [int] NOT NULL,
[FKID] [int] NULL,
CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
(
[PKID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[Table1] WITH CHECK ADD CONSTRAINT [FK_Table1_Table2] FOREIGN KEY([FKID])
REFERENCES [dbo].[Table2] ([PKID])
GO
ALTER TABLE [dbo].[Table1] CHECK CONSTRAINT [FK_Table1_Table2]


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Table2](
[PKID] [int] NOT NULL,
[FKID] [int] NULL,
CONSTRAINT [PK_Table2] PRIMARY KEY CLUSTERED
(
[PKID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[Table2] WITH CHECK ADD CONSTRAINT [FK_Table2_Table1] FOREIGN KEY([FKID])
REFERENCES [dbo].[Table1] ([PKID])
GO
ALTER TABLE [dbo].[Table2] CHECK CONSTRAINT [FK_Table2_Table1]

GO


Insert into Table2
Select 1,NULL

The commands execute successfully.

Am I missing something?



----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Sometimes, winning is not an issue but trying.

You can check my BLOG here

Post #928921
Posted Friday, May 28, 2010 5:37 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 2:41 PM
Points: 815, Visits: 2,020
Without knowning the entities represented by the tables, no answer can be given for why it's wrong, or even that it is wrong, although it probably is. If you tell us what the tables represent, it should be possible to suggest an easier method to model the relationship.


Post #929592
Posted Friday, May 28, 2010 7:56 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 12:43 AM
Points: 448, Visits: 3,337
As Ron says, it's not necessarily wrong from a logical perspective. In fact, relationships of this kind which are mandatory in both directions are perfectly common in data modelling and in business requirements - it's just that SQL Server is a poor tool for implementing such rules.

For practical reasons it's usually necessary to compromise in some way. For example you could populate the tables with some initial data before enabling the constraint(s) or you could remove one of the constraints altogether.


David
Post #929689
Posted Sunday, May 30, 2010 10:33 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 9:41 AM
Points: 3,241, Visits: 4,999
David Portas (5/28/2010)
As Ron says, it's not necessarily wrong from a logical perspective. In fact, relationships of this kind which are mandatory in both directions are perfectly common in data modelling and in business requirements - it's just that SQL Server is a poor tool for implementing such rules.

For practical reasons it's usually necessary to compromise in some way. For example you could populate the tables with some initial data before enabling the constraint(s) or you could remove one of the constraints altogether.



Any specific example? There might be but not seen any situation yet for circular relationships.


----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Sometimes, winning is not an issue but trying.

You can check my BLOG here

Post #930223
Posted Monday, May 31, 2010 2:45 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 12:43 AM
Points: 448, Visits: 3,337
Any specific example? There might be but not seen any situation yet for circular relationships.


I expect you have come across relationships that ought to be mandatory in both directions. It's just that SQL makes it hard or impossible to implement them declaratively and so database designers have got into the habit of ignoring them or having them implemented in application code. Take almost any example of a multi-table relationship that includes the words "at least" or "exactly": Order must have at least one Order Item; every Employee must be assigned to exactly one Department and every Department must contain at least one Employee; every Branch requires exactly one Branch Manager.


David
Post #930280
Posted Monday, May 31, 2010 3:27 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 9:41 AM
Points: 3,241, Visits: 4,999
OK. Let me give an example.
I have one tblEmployee table with EmployeeID as Primary Key and another column DepartmentID.

Another tblDepartment with DepartmentID as Primary Key and another column ManagerID as Foreign Key from the Employee table.

The column DepartmentID in tblEmployee is the foreign Key from the tblDepartment.

Now the above mentioned simple design is implementable and logical. If not, plz let me know. I am expecting many employees against one department. But One department will have only one manager and his ID is refered in the tblDepartment (this will be done by implemented logic).

Secondly, you are right, the logic to keep the tables related two ways should be coded properly. But still, the data in both tables is insertable independently (keeping the Foreign key column NULL).

Please correct me if I am wrong.


----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Sometimes, winning is not an issue but trying.

You can check my BLOG here

Post #930294
Posted Monday, May 31, 2010 3:40 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 12:43 AM
Points: 448, Visits: 3,337
I don't know about wrong or not but it's a different example from the ones I had in mind.

Take a Manager table and a Department table (obviously manager may not be the same thing as employee - not all employees are managers). Now enforce the rule that each department has one manager and each manager has one department.

This is a special case of a 1-N relationship where N=1. In general most 1-N relationships are not easily supported in SQL unless N is allowed to be zero. That's because SQL FOREIGN KEY constraints are always optional on the referencing side of the relationship, ie the "parent" row must exist but a referencing row does not have to exist.


David
Post #930299
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse