SQL Server 2005中的隐形外键(Invisible Foreign Key in SQL Server 2005)
我有一个脚本来通过检查外键的存在来更新数据库，如果它不存在，则创建它。 它由SQL Management Studio生成。
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblChangeRequestForecast_tblCostingCenter]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblChangeRequestForecast]')) ALTER TABLE [dbo].[tblChangeRequestForecast] WITH CHECK ADD CONSTRAINT [FK_tblChangeRequestForecast_tblCostingCenter] FOREIGN KEY([CostingCenterID]) REFERENCES [dbo].[tblCostingCenter] ([CostingCenterID]) GO IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblChangeRequestForecast_tblCostingCenter]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblChangeRequestForecast]')) ALTER TABLE [dbo].[tblChangeRequestForecast] CHECK CONSTRAINT [FK_tblChangeRequestForecast_tblCostingCenter] GO该脚本在运行时会引发错误：
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_tblChangeRequestForecast_tblCostingCenter". The conflict occurred in database "mydatabase", table "dbo.tblCostingCenter", column 'CostingCenterID'.这很神秘。 就查询而言，没有外键的跟踪，但是创建脚本因上述错误而失败。 服务器正在运行SQL Server 2005 SP3（9.00.4035.00）。
[更新]：我刚刚在SQL Server 2012实例上重现了该问题。 所以版本看起来并不太重要。
知道是什么原因引起的吗？
I have a script to update a database by checking for a foreign key's existence and, if it doesn't exist, creating it. It was generated by SQL Management Studio.
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblChangeRequestForecast_tblCostingCenter]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblChangeRequestForecast]')) ALTER TABLE [dbo].[tblChangeRequestForecast] WITH CHECK ADD CONSTRAINT [FK_tblChangeRequestForecast_tblCostingCenter] FOREIGN KEY([CostingCenterID]) REFERENCES [dbo].[tblCostingCenter] ([CostingCenterID]) GO IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblChangeRequestForecast_tblCostingCenter]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblChangeRequestForecast]')) ALTER TABLE [dbo].[tblChangeRequestForecast] CHECK CONSTRAINT [FK_tblChangeRequestForecast_tblCostingCenter] GOThe script raises an error when it's run:
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_tblChangeRequestForecast_tblCostingCenter". The conflict occurred in database "mydatabase", table "dbo.tblCostingCenter", column 'CostingCenterID'.This is very mysterious. There is no trace of the foreign key as far as the queries can tell, but the create script fails with the above error. The server is running SQL Server 2005 SP3 (9.00.4035.00).
[Update]: I've just reproduced the issue on a SQL Server 2012 instance. So the version doesn't appear to be too important.
Any idea what could cause this?
原文：
更新时间：2022-04-06 15:04
最满意答案
使用测试脚本，我发现系数小于1.5。 我的代码看起来像：
tmco = {'NumHeaderLines', 1 , ... 'NumColumns' , 5 , ... 'ConvString' , '%f' , ... 'InfoLevel' , 0 , ... 'ReadMode' , 'block', ... 'ReplaceChar' , {',.'} } ; A = txt2mat(filename, tmco{:});注意不同的'ReplaceChar'值和'ReadMode''块'。
我在我的（不是太新的）机器上获得了大约5MB文件的以下结果：
- txt2mat test comma avg。 时间：0.63231
- txt2mat test dot avg。 时间：0.45715
- textscan test dot avg。 时间：0.4787
我的测试脚本的完整代码：
%% generate sample files fdot = 'C:\temp\cDot.txt'; fcom = 'C:\temp\cCom.txt'; c = 5; % # columns r = 100000; % # rows test = round(1e8*rand(r,c))/1e6; tdot = sprintf([repmat('%f ', 1,c), '\r\n'], test.'); % ' tdot = ['a header line', char([13,10]), tdot]; tcom = strrep(tdot,'.',','); % write dot file fid = fopen(fdot,'w'); fprintf(fid, '%s', tdot); fclose(fid); % write comma file fid = fopen(fcom,'w'); fprintf(fid, '%s', tcom); fclose(fid); disp('-----') %% read back sample files with txt2mat and textscan % txt2mat-options with comma decimal sep. tmco = {'NumHeaderLines', 1 , ... 'NumColumns' , 5 , ... 'ConvString' , '%f' , ... 'InfoLevel' , 0 , ... 'ReadMode' , 'block', ... 'ReplaceChar' , {',.'} } ; % txt2mat-options with dot decimal sep. tmdo = {'NumHeaderLines', 1 , ... 'NumColumns' , 5 , ... 'ConvString' , '%f' , ... 'InfoLevel' , 0 , ... 'ReadMode' , 'block'} ; % textscan-options tsco = {'HeaderLines' , 1 , ... 'CollectOutput' , true } ; A = txt2mat(fcom, tmco{:}); B = txt2mat(fdot, tmdo{:}); fid = fopen(fdot); C = textscan(fid, repmat('%f',1,c) , tsco{:} ); fclose(fid); C = C{1}; disp(['txt2mat test comma (1=Ok): ' num2str(isequal(A,test)) ]) disp(['txt2mat test dot (1=Ok): ' num2str(isequal(B,test)) ]) disp(['textscan test dot (1=Ok): ' num2str(isequal(C,test)) ]) disp('-----') %% speed test numTest = 20; % A) txt2mat with comma tic for k = 1:numTest A = txt2mat(fcom, tmco{:}); clear A end ttmc = toc; disp(['txt2mat test comma avg. time: ' num2str(ttmc/numTest) ]) % B) txt2mat with dot tic for k = 1:numTest B = txt2mat(fdot, tmdo{:}); clear B end ttmd = toc; disp(['txt2mat test dot avg. time: ' num2str(ttmd/numTest) ]) % C) textscan with dot tic for k = 1:numTest fid = fopen(fdot); C = textscan(fid, repmat('%f',1,c) , tsco{:} ); fclose(fid); C = C{1}; clear C end ttsc = toc; disp(['textscan test dot avg. time: ' num2str(ttsc/numTest) ]) disp('-----')With a test script I've found a factor of less than 1.5. My code would look like:
tmco = {'NumHeaderLines', 1 , ... 'NumColumns' , 5 , ... 'ConvString' , '%f' , ... 'InfoLevel' , 0 , ... 'ReadMode' , 'block', ... 'ReplaceChar' , {',.'} } ; A = txt2mat(filename, tmco{:});Note the different 'ReplaceChar' value and 'ReadMode' 'block'.
I get the following results for a ~5MB file on my (not too new) machine:
- txt2mat test comma avg. time: 0.63231
- txt2mat test dot avg. time: 0.45715
- textscan test dot avg. time: 0.4787
The full code of my test script:
%% generate sample files fdot = 'C:\temp\cDot.txt'; fcom = 'C:\temp\cCom.txt'; c = 5; % # columns r = 100000; % # rows test = round(1e8*rand(r,c))/1e6; tdot = sprintf([repmat('%f ', 1,c), '\r\n'], test.'); % ' tdot = ['a header line', char([13,10]), tdot]; tcom = strrep(tdot,'.',','); % write dot file fid = fopen(fdot,'w'); fprintf(fid, '%s', tdot); fclose(fid); % write comma file fid = fopen(fcom,'w'); fprintf(fid, '%s', tcom); fclose(fid); disp('-----') %% read back sample files with txt2mat and textscan % txt2mat-options with comma decimal sep. tmco = {'NumHeaderLines', 1 , ... 'NumColumns' , 5 , ... 'ConvString' , '%f' , ... 'InfoLevel' , 0 , ... 'ReadMode' , 'block', ... 'ReplaceChar' , {',.'} } ; % txt2mat-options with dot decimal sep. tmdo = {'NumHeaderLines', 1 , ... 'NumColumns' , 5 , ... 'ConvString' , '%f' , ... 'InfoLevel' , 0 , ... 'ReadMode' , 'block'} ; % textscan-options tsco = {'HeaderLines' , 1 , ... 'CollectOutput' , true } ; A = txt2mat(fcom, tmco{:}); B = txt2mat(fdot, tmdo{:}); fid = fopen(fdot); C = textscan(fid, repmat('%f',1,c) , tsco{:} ); fclose(fid); C = C{1}; disp(['txt2mat test comma (1=Ok): ' num2str(isequal(A,test)) ]) disp(['txt2mat test dot (1=Ok): ' num2str(isequal(B,test)) ]) disp(['textscan test dot (1=Ok): ' num2str(isequal(C,test)) ]) disp('-----') %% speed test numTest = 20; % A) txt2mat with comma tic for k = 1:numTest A = txt2mat(fcom, tmco{:}); clear A end ttmc = toc; disp(['txt2mat test comma avg. time: ' num2str(ttmc/numTest) ]) % B) txt2mat with dot tic for k = 1:numTest B = txt2mat(fdot, tmdo{:}); clear B end ttmd = toc; disp(['txt2mat test dot avg. time: ' num2str(ttmd/numTest) ]) % C) textscan with dot tic for k = 1:numTest fid = fopen(fdot); C = textscan(fid, repmat('%f',1,c) , tsco{:} ); fclose(fid); C = C{1}; clear C end ttsc = toc; disp(['textscan test dot avg. time: ' num2str(ttsc/numTest) ]) disp('-----')
相关问答
更多-
解决方法（直到Google修复此错误）是使用带有android:inputType="numberDecimal"和android:digits="0123456789.," 。 然后将TextChangedListener添加到具有以下AfterTextChanged的EditText中： public void afterTextChanged(Editable s) { double doubleValue = 0; if (s != null) { try { ...
-
您可以通过设置区域设置或使用DecimalFormatSymbols来更改分隔符。 如果要使分组分隔符成为一个点，可以使用欧洲语言环境： NumberFormat nf = NumberFormat.getNumberInstance(Locale.GERMAN); DecimalFormat df = (DecimalFormat)nf; 或者，您可以使用DecimalFormatSymbols类来更改格式化方法生成的格式化数字中出现的符号。 这些符号包括小数分隔符，分组分隔符，减号和百分号等等： De ...
-
使用逗号作为Android EditText的小数分隔符与MvvmCross(Use Comma as decimal separator for android EditText with MvvmCross)[2023-04-23]
这是一个自2009年以来一直没有修复的Android漏洞 。 你必须设置android:digits 。要防止任何区域设置（例如LC_NUMERIC ，这可能是罪魁祸首）影响行为： LC_ALL=C seq 0.1 0.1 0.9 也就是说，我根本不建议使用seq 。 它是一个非标准命令，不保证在所有UNIX平台上都可用，或者在可用时具有任何特定行为。 一个支持浮点的替代方案，请考虑awk ： LC_ALL=C awk -v min=0.1 -v max=0.9 -v inc=0.1 \ 'BEGIN { cur=min; while (cur <= max) { print cur; cur += i ...Virtuoso小数分隔符(Virtuoso decimal separator)[2022-10-26]
Turtle依赖于XML Schema数据类型 ，其中唯一有效的小数分隔符是点。 这些值的后续（重新）表示可能会因区域设置（可能会将小数点分隔符更改为逗号和/或添加千位分隔符）而有所不同，但这似乎是一个不同的问题... （请注意，在撰写本文时，v07.20.3213相当老;建议所有用户（无论是开源还是商业版）更新到当前的v7.20.3217或更高版本。） （ObDisclaimer：我为Virtuoso的制作人OpenLink Software工作。） Turtle relies on XML Schema ...MySQL LOAD DATA INFILE以逗号作为小数分隔符(MySQL LOAD DATA INFILE with comma as decimal separator)[2022-06-23]
尝试将'，'替换为'。' 加载时 例如 - LOAD DATA INFILE 'file.csv' INTO TABLE dados_meteo (@var1, @var2) SET column1 = REPLACE(@var1, ',', '.'), column2 = REPLACE(@var2, ',', '.') Try to replace ',' to '.' when loading. For example - LOAD DATA INFILE 'file.csv' INTO TABLE ...使用逗号作为小数点分隔符验证数字的最佳方法是什么？(What's the best way to validate numbers with comma as decimal separator?)[2024-04-09]
Laravel在验证规则中支持regex模式，因此您可以使用给定模式来匹配类似12,365.00东西，建议在使用正则表达式作为规则时使用数组而不是管道 $rules = array('amount' => array('match:/^[0-9]{1,3}(,[0-9]{3})*\.[0-9]+$/')); 检查这个链接。 另外，如果您想因任何原因删除逗号， 请检查此答案 。 Laravel supports regex pattern in validation rule so you can use ...Matlab：如何用逗号作为小数分隔符读取数字？(Matlab: How to read in numbers with a comma as decimal separator?)[2023-07-10]
使用测试脚本，我发现系数小于1.5。 我的代码看起来像： tmco = {'NumHeaderLines', 1 , ... 'NumColumns' , 5 , ... 'ConvString' , '%f' , ... 'InfoLevel' , 0 , ... 'ReadMode' , 'block', ... 'ReplaceChar' , {',.'} ...使用逗号作为小数分隔符格式化小数，并且没有千位分隔符(Format a decimal with comma as decimal separator and without thousands separator)[2024-01-16]
不错的挑战，这里有一个单行，我发现它适用于您的数据定义。 我试图让它对任意数量的小数都是通用的，但事实证明这很难。 请注意，它不会总是在十进制字符后显示两位数，例如12.50将显示为12,5。 |{ trunc( l_value ) },{ trunc( frac( l_value ) * 100 ) }| 我很好奇其他人发现了什么。 Nice challenge, here is a one-liner I found that works with your data definition. I ...从float转换为字符串时使用格式设置对象。 例如： var fs: TFormatSettings; .... GetLocaleFormatSettings(GetThreadLocale, fs); fs.DecimalSeparator := '.'; 然后当你转换时，这样做，例如： str := FloatToStr(Value, fs); Use a format settings object when converting from a float to a string. For ...相关文章
更多- SQL Server 压力测试
- Hadoop+SQL Server+Excel=大数据分析
- SQL Server 2000 如何锁定表中的行，使其他线程读取该行时候发生sql异常，不能读取？
- Solrflux源码分析-Sql Support within Solr-类Sql的solr搜索实现(2)
- 锋利的SQL
- 一个sql查询
- 《[窗内网]SQL 2008完全学习手册 视频教程》
- 关于mysql 的 sql
- 请教一个SQL实现
- 《Java开放课程: SQL2005视频》共14集包括两个项目实战更新完毕!
最新问答
更多- 您如何使用git diff文件，并将其应用于同一存储库的副本的本地分支？(How do you take a git diff file, and apply it to a local branch that is a copy of the same repository?)
- 将长浮点值剪切为2个小数点并复制到字符数组(Cut Long Float Value to 2 decimal points and copy to Character Array)
- OctoberCMS侧边栏不呈现(OctoberCMS Sidebar not rendering)
- 页面加载后对象是否有资格进行垃圾回收？(Are objects eligible for garbage collection after the page loads?)
- codeigniter中的语言不能按预期工作(language in codeigniter doesn' t work as expected)
- 在计算机拍照在哪里进入
- 使用cin.get（）从c ++中的输入流中丢弃不需要的字符(Using cin.get() to discard unwanted characters from the input stream in c++)
- No for循环将在for循环中运行。(No for loop will run inside for loop. Testing for primes)
- 单页应用程序：页面重新加载(Single Page Application: page reload)
- 在循环中选择具有相似模式的列名称(Selecting Column Name With Similar Pattern in a Loop)
- System.StackOverflow错误(System.StackOverflow error)
- KnockoutJS未在嵌套模板上应用beforeRemove和afterAdd(KnockoutJS not applying beforeRemove and afterAdd on nested templates)
- 散列包括方法和/或嵌套属性(Hash include methods and/or nested attributes)
- android - 如何避免使用Samsung RFS文件系统延迟/冻结？(android - how to avoid lag/freezes with Samsung RFS filesystem?)
- TensorFlow：基于索引列表创建新张量(TensorFlow: Create a new tensor based on list of indices)
- 企业安全培训的各项内容
- 错误：RPC失败;(error: RPC failed; curl transfer closed with outstanding read data remaining)
- C＃类名中允许哪些字符？(What characters are allowed in C# class name?)
- NumPy：将int64值存储在np.array中并使用dtype float64并将其转换回整数是否安全？(NumPy: Is it safe to store an int64 value in an np.array with dtype float64 and later convert it back to integer?)
- 注销后如何隐藏导航portlet？(How to hide navigation portlet after logout?)
- 将多个行和可变行移动到列(moving multiple and variable rows to columns)
- 提交表单时忽略基础href，而不使用Javascript(ignore base href when submitting form, without using Javascript)
- 对setOnInfoWindowClickListener的意图(Intent on setOnInfoWindowClickListener)
- Angular $资源不会改变方法(Angular $resource doesn't change method)
- 在Angular 5中不是一个函数(is not a function in Angular 5)
- 如何配置Composite C1以将.m和桌面作为同一站点提供服务(How to configure Composite C1 to serve .m and desktop as the same site)
- 不适用：悬停在悬停时：在元素之前[复制](Don't apply :hover when hovering on :before element [duplicate])
- 常见的python rpc和cli接口(Common python rpc and cli interface)
- Mysql DB单个字段匹配多个其他字段(Mysql DB single field matching to multiple other fields)
- 产品页面上的Magento Up出售对齐问题(Magento Up sell alignment issue on the products page)