sql server - existing foreign key that references fields with different datatype (char(8) and varchar(8)) - Database Administrators Stack Exchange


this more quest information request help...

i'm upgrading old databases (running on sql 2005 , compatibility level 70, suspect online since sql 6.5) rid of couple of old servers , deploy sql server 2012 or 2014.

i had deal sql collation unsupported dropped indexes , foreign keys. while trying restore them i've been stopped error:

msg 1778, level 16, state 0, line 2
column 'dbo.table1.idcolumn' not same data type referencing column 'table2.columnfk' in foreign key 'foreign_key'.

the first field declared varchar(8) , second char(8) message correct.

i'm doing tests on copy double checked on production database , foreign key there, linking char field varchar field.

when scripting constraint got (that's script giving error):

alter table [dbo].[table2] nocheck add  constraint [foreign_key] foreign key([columnfk]) references [dbo].[table1] ([idcolumn]) go  alter table [dbo].[table2] nocheck constraint [foreign_key] go

i found answer on stating not possible have foreign key using 2 columns have different datatype workaround supplied issue not critical.

the question is: ever allowed create relationship on fields different data types without tricks? there sort of 'compatibility' between char , varchar?

the answer should obvious 'yes, possible' can't find evidence stating behavior (was?) allowed and/or expected: have foreign key in place should not possible create it.

digging long forgotten boxes found both nt4 , sql-server 6.5 installation media , can confirm foreign key referencing fields different data types allowed.

here statement run on sql-server 6.5:

create table tbl1 (code char(8) primary key (code)) create table tbl2 (code varchar(8) primary key (code)) alter table tbl1 add constraint fk foreign key (code) references tbl2 (code)

no trailing semicolon or you'll syntax error.

i can't grasp on sql 7 media can't tell if working on version too.


Comments

Post a Comment

Popular posts from this blog

searchKeyword not working in AngularJS filter -

i writing app using angularjs on front end. want search through table word/field; 1 search box everything. according article here: http://hello-angularjs.appspot.com/searchtable can use filter: searchkeyword this. this code, , not working expected. <label>search: <input ng-model="searchkeyword"></label> <table ng-repeat="post in posts | orderby: sort | filter: searchkeyword"> <tr> <td> {{index(post)}} </td> <td> {{post.title}} </td> <td> {{post.author}} </td> <td> {{post.content}} </td> <td> {{post.date | date: "d/m/yyyy"}} </td> </tr> </table> what should do? thank you try this: controller $scope.posts = [ {title:'title one', author:'author one', content: 'content one'}, {title:'title two', author:'author two', content: 'cont...
Read more

sequelize.js - Sequelize: sort by enum cases -

i have model ( article ) field (the name type ) of type enum('source', 'translated') , return 1 article ordered type field. source articles should returned before translated articles. like: article.findone(order: [ { type: [ 'source', 'translated' ] } ]); unfortunately there appears no built-in solution sorting enums. instead have sort by case , couldn't find documentation on how sequelize. what conventional approach here? sorting case hard in sequelize, sort field (the second answer in linked post) should possible: order: sequelize.fn('field', sequelize.col('type'), 'source', 'translated') it's supported mysql far know though
Read more

user interface - how to replace an ongoing process of image capture from another process call over the same ImageLabel in python's GUI TKinter -

i trying use following code create interactive gui changes color-space(hsv, rbg, grayscale etc) on event of button click. display opencv video in tkinter using multiprocessing being new python having problems multiprocessing , attempts on making gui change it's color space on button clicks hangs entire system. on it's implementation highly appreciated. thankyou. below code: import cv2 pil import image,imagetk import tkinter tk import numpy np multiprocessing import process , queue def quit_it(root,process): root.destroy() process.terminate() def black_andwhite(root,process): process.terminate p=process(target=capture_image, args=(5,queue, )) p.start() root.after(0, func=lambda: update_all(root, image_label, queue)) def update_image(image_label, queue): frame = queue.get() = image.fromarray(frame) b = imagetk.photoimage(image=a) image_label.configure(image=b) image_label._image_cache = b root.update() def update_all(root...
Read more