Primary key over writing the primary key of the child table instead of the secondary key

Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc
Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc used Ask the Experts™
on
The strange thing here is that I have the two table as follows:

(1) Table Inventory (Primary ) with primary key called InventID
(2) Table Productiondetails (Child) with primary key PRID

The two table are supposed to be linked in sql server , but each time I try to use server management studio to directly link these tables , they look like below:

Primary table                                                             Secondary table
 Inventory                                                                    Productiondetails
 InventID                                                                      InventID


But after saving the above relationship , then I try to go and check whether the relationship remain the same as above to my surprise I find that the following is showing:

Primary table                                                             Secondary table
 Productiondetails                                                     Productiondetails
 PRID                                                                                PRID

As a result when the data is posted to this the parent primary key (InventID) will update PRID instead of InventID as foreign key

I'm still new in the SQL server I can't on this!

So what do I do fix this situation, any help will be appreciated.

Regards

Chris
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior Developer
Commented:
Well, I never used the dialogs.. Just script it.

asdsad.png

ALTER TABLE [dbo].[Inventory] 
DROP CONSTRAINT [FK_Inventory_Products]
GO

ALTER TABLE [dbo].[Inventory]  WITH CHECK 
ADD CONSTRAINT [FK_Inventory_Products] FOREIGN KEY([ProductID])
REFERENCES [dbo].[Products] ([ProductID])
GO

ALTER TABLE [dbo].[Inventory] CHECK CONSTRAINT [FK_Inventory_Products]
GO

Open in new window


btw, ProductDetails cannot be a descendent of Inventory. This is semantically wrong.

p.s. you shouldn't use abbreviations. They make it unnecessary hard to work with. Especially if they are not common known abbreviations.

EDIT: And if you're talking about cascading foreign keys, they are of no use here. Products are treated like account entries. Once they are entered, they are never changed. New names or different manufacturer product number must end either in a new product or these data is not stored in the product table itself.
You have a valid point but you are loosing me in deep water somehow, the point here is I want the primary key in table inventory to automatically update the secondary key in table productiondetails NOT what is happening here where the primary key is again over writing the primary key of the child table leaving the secondary key blank , that is where my problem is!


How do I correct this using the simple management studio or if it cannot be done this way , what is the correct code here to correct the situation
 , remember I said I'm still new in SQl server . What am I missing here?


Regards

Chris
ste5anSenior Developer

Commented:
Questions like your require almost every time a concise and complete example. So that we can understand your problem better. Cause code often tells more than 1000 words, especially in your case.
A concise and complete example consists of table DDL (data definition language, the table creation scripts) and sample data as INSERT statements as a single, runnable (thus error free) T-SQL script. Then you can show you problem with a T-SQL script, which shows that behavior. And you can then describe the desired output based on the given scripts.
Why you should post such an example, when it's extra work? Well, cause it is extra work. Often crafting such a sample will show you already the problem.

I want the primary key in table inventory to automatically update the secondary key
What does this mean?

Keep in mind, a primary key should be stable. It should never be modified. Cause normally changing the primary key, means that the real world entity changed. So it's no longer the same thing.

btw, ProductDetails cannot be a descendent of Inventory. This is semantically wrong.
The correct model is

Inventory <-n-1- Products -1-n-> ProductDetails

Open in new window

Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Primary key could change, but you need to use the ON UPDATE CASCADE (and probably also ON DELETE CASCADE) clause in the definition of the foreign key constraint
I have done a proper audit on this , trust me if you import your tables from Ms Access , that is both the parent and the child tables using SSMA which works with MS Access runtime 2013 you have clear broken links between the parent and the child table. If you import a single table without a relation then no issue. The broken link can never be repaired you just have to delete the related table and recreate them manually in using SQL server studio management.

Trust me on this one I have gone in deep without success

Regards

Chris
ste5anSenior Developer

Commented:
Trust me on this one I have gone in deep without success
Okay, can you rephrase your question?

btw, relational theory use a different terminology then Acccess:

relation => table
relationship = > foreign key
tuple => row
attribute => column

So what we normally call relation in our daily life is a relationship between tables, defined in SQL Server as a foreign key.

Links between tables in Access are a utility to make query creation in the designer simpler, so that you don't have to draw them in the designer every time.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial