SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Primary-Foreign Key


Primary-Foreign Key

Author
Message
Krish-853432
Krish-853432
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 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)

Stewart "Arturius" Campbell
Stewart "Arturius" Campbell
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17309 Visits: 7421
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”
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)

Group: General Forum Members
Points: 99577 Visits: 33014
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 Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Atif-ullah Sheikh
Atif-ullah Sheikh
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5122 Visits: 5204
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


RonKyle
RonKyle
SSCertifiable
SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)

Group: General Forum Members
Points: 7092 Visits: 3622
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.



sqlvogel
sqlvogel
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2142 Visits: 3708
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.
Atif-ullah Sheikh
Atif-ullah Sheikh
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5122 Visits: 5204
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


sqlvogel
sqlvogel
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2142 Visits: 3708
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.
Atif-ullah Sheikh
Atif-ullah Sheikh
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5122 Visits: 5204
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


sqlvogel
sqlvogel
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2142 Visits: 3708
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search