Solved

Connect a table to another using FK UNIQUE

Posted on 2010-08-24
9
262 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

832 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