March 16, 2012 at 8:35 pm
how we can set default value to column with foreign key relation .as I have column in table which has foreign key relation with another column.and when i load data into that column it has null value .and for that row i want to set default value -1.i tried various ways but i am getting error
1.i set default value -1 with foreign key relation so when i do by this way i get error The INSERT statement conflicted with the FOREIGN KEY constraint.
2.i set foreign key relation with no default value.and i used derived column in ssis so that whenever there is null value use isnull expression even then i am getting foreign key constraint error.
so how i can set default value for column with foreign key relation?.
March 16, 2012 at 9:10 pm
The default value would need to be a real value in the foreign key table.
An example might be a.n address record has a default value of the key to tbsrate od 'FL'
because axcompany is in Florida,so most of its customers are too.
Did you insert -1 in the foreign table yet?
Lowell
March 16, 2012 at 9:56 pm
That is really the definition of how foreign keys work. The value in the foreign key column MUST exist in the original table. As Lowell said you either need the -1 in the base table or stick with NULL.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 17, 2012 at 3:30 pm
so then can i insert max value of that column instead of default -1 in place of null values.bcz my manager does want null values in foreign key table he wants anything else whenever there is null value in foreign key table.for eg can i insert max value of that column which is 10000 in place of null values.if i can then how ?
March 17, 2012 at 3:47 pm
in the past, because the foreign key table is used for a drop down list, I've seen key-value pairs like 0 'Select...', and the default of zero in the column that will hold that value...what is wrong with that?
if you want -1 as the default value, simp[ly put negative one in the table...it's very simple.
the max value? i don't think that is right. For me, the value must be actually selected, or defaulted...otherwise you are changing data when you are not supposed to.
also, if it's nothing more than not allowing nulls, whatever presentation layer just needs to raise the typical "value is a required field", and force them to select a real value before yuo allow the record to be saved...then you don't need a default value at all..
Lowell
March 17, 2012 at 4:14 pm
weston_086 (3/17/2012)
so then can i insert max value of that column instead of default -1 in place of null values.bcz my manager does want null values in foreign key table he wants anything else whenever there is null value in foreign key table.for eg can i insert max value of that column which is 10000 in place of null values.if i can then how ?
As far of the value - any value - you insert on the FK table of the child table exists as a value in the PK of the parent table you will be fine. That is how referential integrity works, enforced by FK relationship.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy