sql server 2008 - Foreign key or link between part of column selection -

-

i have 3 tables in following setup

create table [dbo].[codevariable] (     [id] [int] null,     [code] [nchar](10) null,     [variable] [int] null ) on [primary]  create table [dbo].[proxy] (     [id] [int] null,     [description] [nvarchar](50) null,     [status] [bit] null,     [added] [datetime] null ) on [primary]   create table [dbo].[wall] (     [id] [int] null,     [description] [nvarchar](50) null ) on [primary]

following values in tables table wall

1   basic wall 2   medium wall 3   advanced wall

table proxy

1   small proxy true    2013-05-08 00:00:00.000 2   medium proxy    false   2013-05-08 00:00:00.000

table codevariable

1   proxy       1          2   proxy       2          3   wall        1          4   wall        2          5   wall        3

owke issue facing, if want insert lets new line in proxy. have id 3, need make sure id 3 exists in codevariable under code proxy!

without foreign key there no check if code exists in code variable.

i have tried foreign keys without success. how can create link between codevariable table on columns code , variable towards table proxy , table wall.

i can created index on code , variable unique. u cannot link foreign key it.

i using sql 2008

thanks

one way change table definitions, foreign key constraints can enforced.

use composite primary key (codeid, codetype) table codevariable (renamed code), codetype can take 2 possible values, either 'p' or 'w'.
(the code had used instead of codetype preferred narrower column keys (primary , foreign) used in indexes.
code converted computed column): 

create table [dbo].[code] (     [codeid] [int] not null,     [codetype] [char](1) not null,     [codename]                               -- computed column          case codetype when 'p' 'proxy'                        when 'w' 'wall'          end,     constraint code_pk         primary key (codeid, codetype),     constraint codetype_ck         check (codetype in ('p', 'w')) ) ;

in 2 other tables, codetype added well, (codeid, codetype) can defined both primary , foreign key:

create table [dbo].[proxy] (     [proxyid] [int] not null,     [codetype] [char](1) not null default 'p',     [description] [nvarchar](50) null,     [status] [bit] null,     [added] [datetime] null,     constraint proxy_pk         primary key (proxyid, codetype),     constraint code_proxy_fk         foreign key (proxyid, codetype)         references code (codeid, codetype),     constraint codetype_proxy_ck         check (codetype = 'p') ) ;   create table [dbo].[wall] (     [wallid] [int] not null,     [codetype] [char](1) not null default 'w',     [description] [nvarchar](50) null,     constraint wall_pk         primary key (wallid, codetype),     constraint code_wall_fk         foreign key (wallid, codetype)         references code (codeid, codetype),     constraint codetype_wall_ck         check (codetype = 'w') ) ;

sql-fiddle


Comments

Post a Comment

Popular posts from this blog

java - Jmockit String final length method mocking Issue -

-
using jmockit 1.2 jar, trying mock string's length method getting unexpected invocation exception: failed: test java.lang.illegalstateexception: missing invocation mocked type @ point; please make sure such invocations appear after declaration of suitable mock field or parameter @ stringdemo.testa$1.<init>(testa.java:17) @ stringdemo.testa.test(testa.java:13) i using testng: @test public void test() throws exception { new expectations() { @mocked("length") string astring; { astring.length(); result = 2; } }; system.out.println(a.showa("test")); } } actual class a: public class { public static int showa(string str){ int a= str.length(); return a; } } this wrong way of recording expected results. shouldn't mock , record string's length() method, record showa() instead. here solution @suppresswarnings("unused...
Read more

asp.net - Razor Page Hosted on IIS 6 Fails Every Morning -

-
i have simple page in intranet uses razor/asp fetch single record table , display it, plus few graphic, dashboard display. meaning, no user intervention other first time open ie internal url. the problem having every morning ie displays "page not found or network error" message. refresh page, , it's stuck , doesn't display anything. try different pc, open ie internal url , it's stuck... ...until following: login server hosting page run inetmgr go web sites, etc right click on page that's giving me problem , select browse at moment, error message: server error in '/' application. -------------------------------------------------------------------------------- type of page not served. description: type of page have requested not served because has been explicitly forbidden. extension '.cshtml' may incorrect. please review url below , make sure spelled correctly. requested url: /application/dashboard.cshtml ---------------...
Read more

ios - Can NSManagedObject conform to NSCoding -

-
i need transfer single object across device. right converting nsmanagedobject dictionary , archiving , sending nsdata. upon receiving unarchiving it. transfer nsmanagedobject archiving , unarchiving instead of creating intermediate data object. @interface test : nsmanagedobject<nscoding> @property (nonatomic, retain) nsstring * title; @end @implementation test @dynamic title; - (id)initwithcoder:(nscoder *)coder { self = [super init]; if (self) { self.title = [coder decodeobjectforkey:@"title"]; //<crash } return self; } - (void)encodewithcoder:(nscoder *)coder { [coder encodeobject:self.title forkey:@"title"]; } @end nsdata *archivedobjects = [nskeyedarchiver archiveddatawithrootobject:testobj]; nsdata *objectsdata = archivedobjects; if ([objectsdata length] > 0) { nsarray *objects = [nskeyedunarchiver unarchiveobjectwithdata:objectsdata]; } the problem above code is. crashes @ self.title in initwithcoder sa...
Read more