Proper foreign keys for MS SQL Server 2005 without UNIQUE constraints? -

-

i have 2 tables (ms sql server 2005) existing application (no db alterations other indexes, etc allowed).

the 2 tables are:

activitydetails (primary table)     activitydetailkey (primary key)  subactivities (child table)     activitydetailkey (refers activitydetails)

now, there no contraints on subactivities activitydetailkey. basically, each activitydetail row, there can many subactivities rows , there nothing stopping user deleting activitydetails row , leaving subactivities orphaned.

there supposed "soft locks" in application prevent isn't working , wanted put better integrity in db layer too.

but can't seem add foreign key because subactivities's activitydetailkey column isn't unique.

how can prevent people deleting activitydetails rows if there children present?

thanks

edit

i apologize complexity. subactivities table named tedetailsubactivities. changed in question easier read.

but anyway, here gist of complete schema both tables.

https://gist.github.com/840479

i appreciate help.

it sounds you're trying set foreign key wrong way around - if there multiple rows in subactivities same activitydetailkey value, , these references primary key in activitydetails, following should work (based on posted schema, , tested):

alter table tedetailsubactivities add constraint fk_tedetailsubactivities_activitydetails foreign key       (activitydetailkey) references dbo.activitydetails (activitydetailkey)

previous version, based on table names in post:

alter table subactivities add constraint fk_subactivities_activitydetails foreign key       (activitydetailkey) references activitydetails (activitydetailkey)

there's no uniqueness requirement on activitydetailkey column in subactivities.

as-is, that'll stop deletion of rows activitydetails if there rows in subactivities reference them. if, on other hand, want application able continue deletes, avoid leaving orphaned rows in subactivities, add on delete cascade after final closing bracket above.

the above works based on following table definitions. if doesn't work in database, need out posting either actual table definitions database, or "close enough" mimic you're seeing:

create table activitydetails (     activitydetailkey int not null primary key ) go create table subactivities (     activitydetailkey int not null ) go

sigh. if you're going insist on using ssms designers:

  • right click on subactivities, choose "design".
  • press "relationships" toolbar button
  • press "add"
  • press "..." against "tables , columns specification" property
  • in "primary key table" drop down, choose "activitydetails"
  • in grid below, choose activitydetailkey on both sides
  • press "ok", "close", "save" toolbar button, , (if necessary) "yes" save warning
  • close designer.

Comments

Post a Comment

Popular posts from this blog

Javascript line number mapping -

-
magically formatted comments change reported line number of javascript errors in browsers; this: //@line n "f" n line number , f file name. unfortunately, //@line appears ungoogleable. know there documentation on feature, , browsers support it? (i found references here , here .) from can find available in mozilla based browsers. it using js_setoptions command can turn these options on or off depending on values passed. https://developer.mozilla.org/en/js_setoptions
Read more

c# - Is it possible to remove an existing registration from Autofac container builder? -

-
something along lines: builder.registertype<mytype>().as<itype>(); builder.registertype<mytype2>().as<itype>(); builder.deregistertype<mytype>().as<itype>() var container = builder.build(); var types = container.resolve<ienumerable<itype>>(); assert.istrue(types.count == 1); assert.istrue(types[0].gettype == typeof(mytype2)); scenario: go through bunch of assemblies , go register types want make sure have 1 implementation of given type. need before create container. track on own nice if autofac me bit. this cannot done directly using containerbuilder , unless start on new one. mind you, having first built container should able construct new container filtering away unwanted types , reusing registrations first container. this: ... var container = builder.build(); builder = new containerbuilder(); var components = container.componentregistry.registrations .where(cr => cr.activator.limittype != typ...
Read more

php - Mysql PK and FK char(36) vs int(10) -

-
i have read mysql can handle indexes , searches better if tables keys integers rather char(36) uuid's. is worth converting int(10)? note: use 1 server , have have plans use multiple databases concurrently therefore removing 1 of big reasons using uuid. our tables innodb if makes difference. thanks in advance. usually rdbms can handle integer keys pk more efficient, other datatypes. reason how build index column, yes: long dont require string (or other typed) keys, should use integers. however: char(36) , int(10) far away being equal, because int(10) much smaller, char(36). dont know, if require many different keys, should keep in mind. update, complete last paragraph: int(10) 32-bit, char(36) 36 - ^byte^ (= 288-bit). not mean, int consumes less space, means, char(36) has 4 times more different keys.
Read more