sql server - Using Composite Key Or Unique Constraint to prevent row duplicate -


my table , data looks this: data being mine archive.

table duplicate row

i using row_number() over(partition values) single row. however, add constraint table prevent duplicate inserts.

because data many many business rule, figure can either use composite key comprising userid , 4 additional columns.

i looking @ following 3 options:

  1. should use constraint comprising of columns below
  2. use composite key comprising of columns
  3. add unique column (clustered index) , use constraint on rest of columns.

however, using composite still able insert duplicate rows.

what better approach here?

tl;dr primary key unique constraint should way go.

you should add new column serve primary key. identity column fine. like:

alter table table_1    add id int identity;  alter table table_1    add constraint pk_table_1    primary key(id);

the reason makes easier trace row individual entity can more perform updates , deletes. plus, it's useful 3rd normal form.

the primary key not have clustered index, btw. although clustered index created automatically default primary key in sql server if there not clustered index on table.

once have primary key, can use unique constraint other columns. that's assuming ok default system error messages. if need error messages more customized needs want use instead of trigger.

i avoid clustered, unique index columns. cause lot of fragmentation , disk io when inserting new records. non-clustered unique index ok, you've got constraint @ point.


Comments

Post a Comment

Popular posts from this blog

php - SPIP: From Tag directly to an article -

i work spip , plugin called tagsphere works fine tags in spip. have tag groupe put 1 article tag. when click on tag in tagsphere send me before menu can see articles linked tag. want send me directly article without see tag menu before. here code model of thagsphere: <div id="tagsphere-#env{id_article}"> <ul> <boucle_mot(mots){id_groupe ?}> <li>[<a href="#url_mot">(#titre)</a>]</li> </boucle_mot> </ul> </div> anybody idea? if want skip tag page, have link directly article. <b_article> block prevents display tag if no article linked. <div id="tagsphere-#env{id_article}"> <ul> <boucle_mot(mots){id_groupe ?}> <b_article> <li> <a<boucle_article(articles){...
Read more

jquery - isAjaxRequest always return false -

i got error message 404,'model has not been saved' following code my action controller public function actionextend($id) { $model=$this->loadmodel($id); if(yii::app()->request->isajaxrequest) { $model->todate = date('y-m-d h:i:s a',(strtotime('next month',strtotime(date('y-01-d'))))); if($model->save()) { yii::app()->end(); } else { throw new chttpexception(404,'model has not been saved'); } } } the jquery call $("#buttonextend<?php echo $this->post_row;?>").click(function(e){ $.ajax({ type: "post", url: "<?php echo yii::app()->createurl('post/extend', array('id' => $data['id'])); ?>", }); }); why not ajax call ? thank help i found response, missed } , function ...
Read more

ruby on rails - In a controller spec, how to find a specific tag in the generated view? -

i know integration tests preferred need ran in controller test, i'm testing gem injecting html code in view, xhr can't run in feature spec (if can, please explain me how :) ) so rspec controller tests can assert selector present (with capybara) : response.body.should have_selector('#foobar') has_selector? call all method capybara find selector. what want last child of body , assert id in particular. afaik it's not possible have_selector. what : all('body:first-child').first.id.should == '#foobar' however, capybara dsl, defined (more or less): def all(*args) page.all(*args) end and page empty unless use visit it's integrations specs. how can use capybara all method inside rspec controller test ? i can't test right after googling seems trick def page capybara::node::simple.new(response.body) end source
Read more