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
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 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
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10952 Visits: 7325
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 (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)

Group: General Forum Members
Points: 54692 Visits: 32783
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
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4116 Visits: 5201
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
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3736 Visits: 3550
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
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1038 Visits: 3706
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
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4116 Visits: 5201
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
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1038 Visits: 3706
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
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4116 Visits: 5201
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
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1038 Visits: 3706
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