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

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
LVL 3
Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.ScDirectorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.ScDirectorAuthor Commented:
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 DeveloperCommented:
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

Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.ScDirectorAuthor Commented:
Thank you
Giuseppe PizzutoIT DirectorCommented:
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
Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.ScDirectorAuthor Commented:
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 DeveloperCommented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.