Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Connect a table to another using FK UNIQUE

Posted on 2010-08-24
9
Medium Priority
?
309 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
  • 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
[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

 

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 2000 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…

564 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