Foreign key from part of a primary key
-
hi,
I'm trying to achieve something and I can't really get it to work.
I apologize in advance if it's been asked before, please point me to the proper thread if that's the case.
Here's the situation: I have a table, TABLE_1 that has 3 fields, 2 of which make up my primary key (there's an ID field and a type field, each ID is unique for a given type so their combination is unique as well).
In another table, I have a field that references the ID of my first table. Now, I know that without the type I cannot technically go to the value I want, but the schema is named in a way that allows me to know which type a field in my second table belongs to.
Now, I'm trying to create a foreign key from my second table to my first table. This foreign key will be disabled and only be used to easily find relationships in the schema. I get an error: "There are no primary or candidate keys in the referenced table <table> that match the referencing column list in the foreign key <foreign key>."
I understand this error, and I know why it happens, it makes sense from a relational point of view, but not always from a practical point of view imho. My question is whether there's a way to create a foreign key on a field that is neither unique nor part of a primary key?
Thanks,
Greg
-
As I understand your ID is not unique.
So how SQL Server must know which of ID's in primary table the ID in FK table is referencing?
In other words: can you post INNER JOIN statement joining those 2 tables and not involving hardcoded values?
_____________
Code for TallyGenerator -
What is that you are trying to gain out of this Foreign Key Relationship?
Prasad Bhogadi
www.inforaise.com -
As I understood the post, Greg only wants to create FK "for information", without any functionality :
"This foreign key will be disabled and only be used to easily find relationships in the schema"
I know I will not make Greg happy, but my recommendation is to use FK for what they are designed or not use them at all.
Create IDENTITY column on Table_1 and reference this column instead of referencing non-unique column. Joining tables while using some personal knowledge that is not part of the data is highly non-standard and it will only bring problems in the future.
-
Hi,
First of all thank you for the answers, they are helpful. And by all relational means, make sense. The reason I was trying to achieve this is that this particular table is used to store enumerated values about an object. for example, i have a table to store car objects. cars have a type (suv, etc...), which is an enumerated value. Storing each enumerated value in a separate table (one table for car types, one table for transmission types etc...) will currently leave me with about 150 more tables, each containing about 5 rows at most.
Before, all our enumerated values were defined in type libraries and not in the database, which made reporting complicated and always required us to provide explanations regarding what each value meant. we are trying to improve on this by storing our enumerated values' descriptions etc... in the database itself.
So, if I want to use FKs, I am left with 2 choices: either add the type to my other tables (does not make sense, when the application saves the car type, to save the fact that this value is the car type), or have one table per enumerated value.
Any ideas how some have gone around this kind of situation before?
Thanks,
Greg
-
Thanks, now I understand better what you meant... If you want to have just one table of types (containing type code, type description and "type of the type", i.e. Table_1), you can do it like this:
Add IDENTITY column, e.g. Type_UID, to Table_1.
Make sure you update all existing rows in tables referencing Table_1 to use the new column Type_UID for referencing. This may include creating new column first, updating it and then dropping the old column.
Create FK in tables referencing Table_1 using Type_UID.
Or is there something that does not allow you to go this way?
-
Hi,
Thanks for the reply. Well, I could go this way. however, here's the catch. That UID you specify works well in the relational world. However, in a type library (to continue with my example), a car type is a sequence of integers (1, 2, ...) and a transmission type another one (1, 2, ...).
Hence, for my objects: car->getType() returns 1 or 2 and car->getTransmissionType() returns 1 or 2 as well, but in the programming environment, one is of type cartype and another of transmissiontype. The thing is that once sent to the database, these are converted to integers, namely 1,2,... and 1,2,... so without creating 150 tables, I see two choices:
1- Create a unique ID and a related column in my car table. have a trigger fill the proper value on insert/update, since the programming environment will know nothing of these "master" UIDs.
2- For each column that is a type in my car table, add another column that specifies the type. and make the FK on the combination of both, just like in my enumerated values table. once again, the type column of the car table would have to be filled in on insert/update automatically for the same reason. however, all rows for a car would have cartype = 1, which, well, says a lot about how relevant this attribute is in that case. that's the original reason why i had used a special naming scheme to find a type based on a column's name.
Let me know if this is not clear, I may be able to pull some ASCII drawing out
Greg
-
What I mean is this...
There are two different things: One is how the data are stored in the database, another is how they are displayed to the user. ID column values don't need to have any meaning and in fact it is better if nobody knows them. You simply join the tables using your identity column, but display the respective code (i.e. 1, 2, ...).
You can either use view with instead of triggers, or code the insert/update directly in the application, or maybe use stored procedures.
User enters a new car and chooses car type = 2. Your application code (or SP) can first retrieve the Type_UID value for type_code=2 AND type_type = 'car', and then perform insert using this value.
-
I see what you mean. It makes sense but may or may not be feasible, at least in the short term, because of the amount of work and code rewrite involved in such a process. Considering the fact that a lot has already been done in that way for the past month, I don't know how much more we'll be able to spend before the next big dev cycle.
In any case, thanks for the answers, I enjoyed the discussion and will definitely look more into it with your suggestions. They are good and go in the way I feel like I should go, but whether or not we'll have the time and resources to do it is a different story
(as much as I wish I could say it should not matter if that's the right thing to do)
Greg
-
They say "think before you do".
Otherwise you'll always end up in enormous "amount of work and code rewrite involved "
_____________
Code for TallyGenerator -
Oh they thought
. And they thought well about a lot of things. I don't mean to offend when I say this but a lot of hardcore programmers can code like crazy monkeys while seeing a database only as a big text file for storage: how the data is stored and what use can be made out of it is not really their concern. When a product reaches a certain maturity though, then the database's potential power becomes obvious and that's when the "think before you do" comes into play. I find this gap between programming and databases quite interesting actually. Databases are not important to a lot of programmers out there. Almost like OO and DB can't live without one another but don't really get along when you go beyond simple applications (no matter what language you use).
A lot of the work I've been doing for the past month has been to standardize naming conventions, index names etc... and overall performing a cleanup of the database schema. This required to go through all the applications' code to change constants etc.. that were not always constants (ahem). Funny thing is that no matter how much I and others say "It's not good", I can probably find these types of errors in every application written by anybody.
In any case, as far as my "little" problem is concerned, I still do not have a solution that I consider as both valid from a DB and OO point of view... I'm going to bring it up tomorrow and we'll see what comes out of it.
Greg
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply