What am I missing? Aaron S. Aaron S Aaron S 3 4 4 bronze badges. That generally means you have a value in the EditedBy column that has no matching value in User. Be careful here. Sean, that's what I've been finding online so far. But I just created the EditedBy column right before trying to apply the foreign key, so every record present has a NULL value currently. And as far as deleting users, I never delete a record.
I have a delete flag so that user accounts can be recovered and so that I can avoid the very issue you're describing. Add a comment. Active Oldest Votes. Obviously that wont work. I fixed the statement to use the correct field in the [data] table and all is well. You can accept your own answer to show others that your question has a satisfactory answer.
Sign up or log in Sign up using Google. Sign up using Facebook. Empty cells are '' not NULL! If you changing your "NULL" values to none and it works, it might just be that there's an empty string or a hidden character in there. Sign up to join this community. The best answers are voted up and rise to the top. Stack Overflow for Teams — Collaborate and share knowledge with a private group.
Create a free Team What is Teams? Learn more. Asked 9 years, 3 months ago. Active 9 years, 3 months ago. Viewed 19k times. Improve this question. A-K 7, 3 3 gold badges 30 30 silver badges 49 49 bronze badges. Tony Tony 1 1 gold badge 2 2 silver badges 5 5 bronze badges. What is the exact error message you are getting? Are you sure you're creating the FK in the right direction? That would report the same error message as well. Bodza Bodza.
I'm having an argument with an incumbent self-taught programmer that it is OK to use null foreign keys in database design. My take is the whole point of a foreign key is that it's not supposed to be optional, it's very definition is it's a necessary link to the parent table and part of the definition. If it's optional it shouldn't be part of the definition of a table and should be in a linking table instead.
Follow Post Reply. David Portas. In general it's good practice to keep NULLs to a minimum in any database schema. However there's nothing fundamentally wrong with having a nullable Foreign Key constraint. Erland Sommarskog. I agree with your programmer. There are those who hold the ideal that there should be no nullable columns in database, but if you move all optional columns into linking tables, you will drown in tables.
Say that you in your business process you have an Order. An Order has a customer - he is probably known from the beginning. There is an employee who is responsible for assembling the order - but he is not known until the order is assigned to someone.
The Order is later shipped, and added to a shipment, and thus there is a Shipment ID. At the same time there is an invoice ID. Yet, the customer pays, and now you have a payment ID. If you insist on linking tables we are now up to four.
Since it would be an error to have a payment ID, if there is not an invoice ID, you need enforce that restriction with a trigger, rather with a CHECK constraint on the table. Personally in this example I would say that the individuals at the root of the tree are their own managers.
Give me a better example to convince me. I take it you prefer denormalization to normalization then? And that would be a problem because? Your point about not knowing the employee key at the time of the order is like modelling someone's current state of health: should the current state of health have a nullable DIEDON key because part of the life process is to die at a later date?
Likewise if you can't assign the foreign key to the table at the time, then it's NOT a foriegn key: if you insist a nullable column should be in the table, then it's just not good practice to allow it to be nullable. Otherwise, why not just forget about referential integrity alltogether and just enforce "integrity" in the application.
Sorry I don't buy it. In the case where you are deriving a foreign key from simply a unique column then, sure you can have a null in the unique column, but that's the same as entering a dummy value into the unique column to reference it. Sorry, but I've been designing databases since SQL 6. No, that is a forgone conclusion. My approach to data modelling is foremost practical. Denormalizing by duplicating columns can lead to performance issues. On the other hand, keeping aggregated tables with the sum of transactions is almost a necessity in the system I work with.
Because the logical information is scattered over four tables, which makes it more difficult for the programmer to understand and use, and makes it more difficult in support situations to view the data. I don't know how big your data models are, but the system I work have over tables, and clarity for the developers is important.
Dates are a good examples of why should not split into several tables. In our database there is a table for sendouts. A document can be sent to more than one address for the same account. Most commonly a sendout applies as long as the information is there, in which case both fromdate and enddate are NULL. But an account owner may want extra copies to his summer house for a limited period.
Or an account owner may be under age, and copies are to be sent to his guardian until his 18th birthday. Thus, in this case fromdate is NULL, but enddate has a value. And that would be two four-column tables, unless we introduce a surrogate key for sendouts. And how would we use this table? In which case the NULLs reappears. Of course, you could have one stored procedures per table, but that would be ineffecient, and you would leave it to the client to perform the join to reassociate the data.
And the client still have to keep track of a concept of "not applicable". So instead of bending over backwards to avoid NULL in the database, it appears a lot simpler to have the columns in one place from the beginning. After all, the NULLs are there already in the business rules. Referential integrity and nullability are orthogonal issues to each other.
Referential integrity means that if an attribute has a value, it must be defined in another table. None of the concepts imply the other. But you are mixing apples and oranges. But CampaignID is nullable, because not all order items are bought because of an offer from a campaign. Of course, database design is an area where you often can do things in more than one way, and it's up to subjective opinion which you prefer. However, the model you seem to advocate, when take to its extreme, would lead to data models that are difficult to use, and not always that effecient.
Agreed, it is also in some cases practical, especially if reporting.
0コメント