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

What is the difference between data design and data model(ERD) -

-
i have google question , find answer in day. know, data design many many table have list column,and mark pk , fk. why er-diagram have find this? data design process of designing database. main output of data design detailed logical data model of database. some people data design includes of needed logical , physical design choices , physical storage parameters needed generate design in data definition language. technically, database analysis, database analysts (dbas) trained do. while person can both data design , database analysis, these 2 different tasks. data design models data. database analysis takes model , applies 1 or more database engines (relational, hierarchical, nosql). a logical data model 1 of main outputs of data design. data model represented entity relationship diagram, or er diagram.
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