Connect a table to another using FK UNIQUE

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
kenuk110Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
cyberkiwiConnect With a Mentor Commented:
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
 
cyberkiwiCommented:
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
 
cyberkiwiCommented:
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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
kenuk110Author Commented:
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
 
kenuk110Author Commented:
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
 
cyberkiwiCommented:
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
 
kenuk110Author Commented:
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
 
kenuk110Author Commented:
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
 
kenuk110Author Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.