Solved

Connect a table to another using FK UNIQUE

Posted on 2010-08-24
9
287 Views
Last Modified: 2012-05-10
Hi,

I have two tables, one is STORE and the other is BLOCK. I have blocks within stores.

I had to update the UNIQUE key in the STORE table so it includes:

countryCode, cityCode, storeCode, vendorCode, storeType, classCode - classCode being the new part of the UNIQUE key.

My other table, BLOCK, connects to this as a FOREIGN KEY but got disconnected when I included classCode in the STORE table.

I still need this link to be there but when I try to reconnect BLOCK to STORE it says I have to have classCode in the BLOCK table. I really don't need that in that table so I'm lost as to what to do?

Any help would be much appreciated.

Regards,

Ken
0
Comment
Question by:kenuk110
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33508432
A suggestion, you should design your tables like this:

STORE
ID - identity, primary key clustered
countryCode, cityCode, storeCode, vendorCode, storeType, classCode - Unique constraint
other columns

BLOCK
STORE_ID - FK to Stored.ID
countryCode, cityCode, storeCode, vendorCode, storeType - DROP all of these
other columns
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33508447
If you really must  continue to use FK against
"countryCode, cityCode, storeCode, vendorCode, storeType"
Then you can create a 2nd Unique Constraint, so that your table (very inefficiently) has

STORE
countryCode, cityCode, storeCode, vendorCode, storeType, classCode - Unique constraint
countryCode, cityCode, storeCode, vendorCode - non-primary key unique constraint
other columns

BLOCK
countryCode, cityCode, storeCode, vendorCode, storeType - FK to STORE on all these fields
other columns

Such a long key value is not going to perform well by any measure.
0
 

Author Comment

by:kenuk110
ID: 33518472
Hi,

Really sorry for the delay, I've got 101 things going on! Not your problem I know and I should have checked this as I did ask the question!

I'll try this when I get in to work, really appreciate your feedback.

Regards,

Ken
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

Author Comment

by:kenuk110
ID: 33519008
Hi,

I thin kit's my knowledge of database, or lack of. This is what I'm trying to do;

I have Country, City, Store, Block, Item tables. I work for a merchandising company and we merchandise products in supermarkets, shampoos etc, just a bit of background as to why I have tables named like this.

I want it so you cannot add something to a child table unless it's in a parent table. I'm sure I've got it wrong but I'm learning and at the moment it seems to 'work', however, I do completely agree that it's not the best way of doing it but I don't know what is so it's trial and error for me.

Example of the tables;
Country countryCode, countryName,pk
City: countryCode, cityCode, pk
Store: countryCode, cityCode, storeCode, storeType, storeName, vendorCode

as I go down to the item table I need each table o only have values I have put in the preceding tables, this is the only way I knew how.

I made a single PK in each, identity and Increment and also bigint then I made a Unique FK to each parent so you couldn't place something in the table if it didn't exist. A bottle of shampoo must have a 'block' to sit on in the store, the block must have a store to be inside, a store must be in a city and the city must be in a country. It was the only way I knew how to constrain the entries.

Is this totally wrong?

Thanks for any advice you can give.
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33519397
That is all correct.

But you can a bigint identity column to Store itself, which in turn is referenced by Block.

Store: Store_ID, countryCode, cityCode, storeCode, storeType, storeName, vendorCode
Block: Store_ID, ... other columns

I think you have it like this now right?

Store: countryCode, cityCode, storeCode, storeType, storeName, vendorCode
PK on some combination of fields, at least the codes
Block: multiple fields to join to PK on Store table.
0
 

Author Comment

by:kenuk110
ID: 33528427
Hi,

Yes, the STORE code is a make up of all of those, the PK is just a PK field in the STORE table. I thought I needed just one PK field then I'd make the 'proper' code just UNIQUE.

We have to name and number our stores ourselves. Are you saying I should let the 'system' just make me a store code?

I think I'm confusing things here, I'm not too good at this!

The thing about our coding is that we want to pre define the codes for our stores, blocks and items etc. The stores are just made up of the next available number for that city, the blocks refer to a grid reference we have when we physically go in to a supermarket and floor plan it, its simple, just a grid reference, A1-ZZ99, the items on the blocks, the numbers for these come from a piece of software that numbers them itself then we input them in our system.

Sorry for the long description but I don't want you to be suggesting something due to info I haven't told you about, wouldn't be fair.

Regards,

Ken
0
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 500 total points
ID: 33530291
Let me understand.

I had to update the UNIQUE key in the STORE table so it includes:

The UNIQUE key on STORE was: countryCode, cityCode, storeCode, vendorCode, storeType
The UNIQUE key on STORE was: countryCode, cityCode, storeCode, vendorCode, storeType, classCode
The PRIMARY key on STORE is: ?

The FK on BLOCK to STORE was: countryCode, cityCode, storeCode, vendorCode, storeType
FK was from BLOCK to STORE.UNIQUE-key
It is now missing.

Have I got it right?

I think you should set up the FK from the BLOCK field/s matching the shorter STORE.PRIMARY-key

Regards
Richard
0
 

Author Comment

by:kenuk110
ID: 33545992
Hi,

The first bit is correct yes, the UNIQUE key now on STORE is what you put.

The second part is also correct, the BLOCK is FK'd to STORE but I had to include classCode in the BLOCK table to allow me to do this.

I'm sure I'm being stupid here and it's my lack of knowledge on this subject but are you saying that for the BLOCK table I just need to link the PK from BLOCK to the PK in STORE? Will that 'make' the values that go in to BLOCK ONLY what is in STORE?

Again, really sorry to be thick here!

Regards,

Ken

0
 

Author Closing Comment

by:kenuk110
ID: 33565767
Thank you for your help on this. I'll close it now as the answer was given ages ago, it was just me asking another question. Really appreciate your help.

Best Regards,

Ken
0

Featured Post

Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question