Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rrb
SQLTeam Poet Laureate
1479 Posts |
 Posted - 2010-04-13 : 03:54:45
|
| hi yaksI know this is not standard practice, but for various reasons I'm wanting to define a foreign key reference to a column of comparable but different length/precision etc (in this case decimals of different precisions). Is there a way to turn something off to allow this is SQL Server?You can do this in Oracle and MySQL provided the types are sufficiently comparable.I've searched the forums with no luck. The only reference about this in BOL that I can find suggests you might be able to - it says "Related columns must have the same data type (or data types that can be related) and length property settings."Various other pages in BOL suggest they have to be identical types, but I'm curious given the previous quote specifically appears to suggest otherwise.Anyone tried doing this before? Is there a switch?Thanks in advance(please don't think I'm rude if I don't respond straight away, I'm going home now...)--I'm not schooled in the science of human factors, but I suspect that surprise is not an element of a robust user interface. |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2010-04-13 : 04:06:24
|
i think might be able to reference using persisted computed column
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2010-04-13 : 04:13:00
|
[code]create table t1( col1 decimal(12,2), col2 int, constraint pk_t1 primary key (col1))create table t2( col1 int, col2 decimal(10,2), col2a as convert(decimal(12,2), col2) PERSISTED )alter table t2 add constraint fk_t2_t1 foreign key (col2a) references t1 (col1)[/code]http://msdn.microsoft.com/en-us/library/ms191250.aspxquote:
Computed columns used as CHECK, FOREIGN KEY, or NOT NULL constraints must be marked PERSISTED.
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2010-04-13 : 18:47:40
|
| Thanks khtan - still getting used to the SQL2008 way...that'll do well enough.--I'm not schooled in the science of human factors, but I suspect that surprise is not an element of a robust user interface. |
|
|
|
|
|
|
|
|
not same length as referencing column