Primary-Foreign Key

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Development » Primary-Foreign Key

18 posts, Page 1 of 2

1 2 »»

Primary-Foreign Key

Rate Topic

Display Mode

Topic Options

Author

Message

Krish-853432

Posted Tuesday, May 25, 2010 3:18 AM

Forum 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

Stewart "Arturius" Campbell

Posted Tuesday, May 25, 2010 4:32 AM

Hall of Fame

Group: General Forum Members
Last Login: Yesterday @ 3:48 AM
Points: 3,125, Visits: 4,311

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

Grant Fritchey

Posted Tuesday, May 25, 2010 6:06 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 3:51 AM
Points: 13,372, Visits: 25,154

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

Atif-ullah Sheikh

Posted Thursday, May 27, 2010 6:13 AM

Hall of Fame

Group: General Forum Members
Last Login: Thursday, March 14, 2013 4:15 AM
Points: 3,240, Visits: 4,960

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

RonKyle

Posted Friday, May 28, 2010 5:37 AM

Say Hey Kid

Group: General Forum Members
Last Login: Yesterday @ 6:43 AM
Points: 671, Visits: 1,503

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

sqlvogel

Posted Friday, May 28, 2010 7:56 AM

SSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 12:15 AM
Points: 406, Visits: 2,851

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

Atif-ullah Sheikh

Posted Sunday, May 30, 2010 10:33 PM

Hall of Fame

Group: General Forum Members
Last Login: Thursday, March 14, 2013 4:15 AM
Points: 3,240, Visits: 4,960

David Portas (5/28/2010)

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

sqlvogel

Posted Monday, May 31, 2010 2:45 AM

SSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 12:15 AM
Points: 406, Visits: 2,851

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

Atif-ullah Sheikh

Posted Monday, May 31, 2010 3:27 AM

Hall of Fame

Group: General Forum Members
Last Login: Thursday, March 14, 2013 4:15 AM
Points: 3,240, Visits: 4,960

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

sqlvogel

Posted Monday, May 31, 2010 3:40 AM

SSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 12:15 AM
Points: 406, Visits: 2,851

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 »

18 posts, Page 1 of 2

1 2 »»

Permissions