Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Connect a table to another using FK UNIQUE

Posted on 2010-08-24
9
Medium Priority
?
303 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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

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 …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

610 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