sql server - Why does this T-SQL OUTPUT INTO with FOREIGN KEY hack work? -

-

the base example lifted no way use tsql output normal foreign key constraints?; code fails expected. if way constraint defined in example modified below, defining fk constraint with nocheck , checking it, output run unimpeded.

this seems contradict output clause docs. specifically:

output_table [the table receiving into] cannot:

•have enabled triggers defined on it.

•participate on either side of foreign key constraint [emphasis added].

•have check constraints or enabled rules.

from relational perspective below could work, action supposed precluded. if fk defined straight "with check" (the default) fails expected. if defined "with nocheck" , enabled "check constraint" it, well, fails fail.

it awesome if known, supported feature. or did find bug in sql that's persisted since @ least sql 2008 (i tested 2008 , 2014)? why work? why shouldn't it? risking using it?

if object_id ('dbo.forn') not null begin     alter table dbo.forn drop constraint fk_forn_prim     drop table dbo.forn; end if object_id ('dbo.prim') not null     drop table dbo.prim; go  create table dbo.prim (c1 int primary key); create table dbo.forn (c1 int ); alter table dbo.forn nocheck add constraint fk_forn_prim foreign key (c1) references dbo.prim(c1); alter table dbo.forn check constraint fk_forn_prim ; go  -- in fact fail foreign key constraint violation insert dbo.forn values (2);  -- works!! insert dbo.prim     output inserted.c1 dbo.forn select 1;

the foreign key still not trusted system after check statement. because default existing constraints nocheck, you're running:

    alter table dbo.forn nocheck check constraint fk_forn_prim;

correct statement re-enable is:

    alter table dbo.forn check check constraint fk_forn_prim;

the nested insert fail again after running that. not recommended leave fk untrusted sql not consider many operations.

you can check untrusted fk's in system view sys.foreign_keys, is_not_trusted field.

more here: http://sqlblog.com/blogs/hugo_kornelis/archive/2007/03/29/can-you-trust-your-constraints.aspx


Comments

Post a Comment

Popular posts from this blog

node.js - Mongoose: Cast to ObjectId failed for value on newly created object after setting the value -

-
this question has answer here: what's mongoose error cast objectid failed value xxx @ path “_id”? 7 answers even though has been asked many times because entity has no valid objectid field. i came across situation objectid set value can casted objectid still error. the schema: var serviceschema = parammongoose.schema({ servicename:{ type: parammongoose.schema.types.objectid , ref: 'servicenames',required:true } }); pseudo code: servicefromdb=new service({servicename:'some name'}); servicefromdb.servicename='000000000000000000000001'; servicefromdb.save(function(paramerror,paramdata){ if(paramerror){ console.log('but but...',servicefromdb,paramerror) } }); the output of code is: but but... { servicename: 000000000000000000000001, _id: 55079a90286f49280364f78b } { [casterror: cast objectid faile...
Read more

gradle error "Cannot convert the provided notation to a File or URI" -

-
i have defined 2 system properties in gradle.properties: systemprop.builddir=build systemprop.cfgdir=build\\cfg and have following task defined in build.gradle: task clean(group:'clean',description:'clean project') << { ant.sequential { delete(dir: system.properties.builddir) mkdir(dir: system.properties.builddir) delete(dir: system.properties.cfgdir) mkdir(dir: system.properties.cfgdir) } } this generates following error: execution failed task ':clean'. > cannot convert provided notation file or uri: {dir=build}. following types/formats supported: - string or charsequence path, e.g 'src/main/java' or '/usr/include' - string or charsequence uri, e.g 'file:/usr/include' - file instance. - uri or url instance. but equivalent block of code not generate error , works expected: task clean(group:'clean',description:'clean project') << { ...
Read more

Augmenting Latin Hypercube Points in MATLAB -

-
i know commands create lhs design , augment more points later on if model not enough? example, first create 50 points lhs design, add more points (perhaps in batches of 20) incrementally until model accurate enough. example: set1=lhsdesign(5,5); %5x5 matrix %use of set 1, determine more points needed set2=%some command adds 20 points set1 make 25x5 matrix the difficulty in running lhsdesign again 20 new points not take account of original points. there work done using original points , generating new set of points scratch wastes work new points not included in new set. i able make function solves problem. not sure final matrix true latin hypercube, adds required number of points given points , moves them closest available open 'channel' (sub range no point exists) if need be. usage follows: x1=lhsdesign(200,17); xf=lhsaugment(x1,200); which adds 200 points x1 set of points, resulting in xf being 400x17 matrix. function follows: function xf = lhsaugmen...
Read more