12 Replies - 1245 Views - Last Post: 28 June 2012 - 07:09 PM

#1 arrezes  Icon User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 58
  • Joined: 17-October 10

how to detect two columns (not primary key) redundant data insertion

Posted 27 June 2012 - 01:32 AM

I have one table A running on identity for the primary key. two columns inside table A, eg 1 & 2 have other columns or data dependent on them. When adding new record to the table I want to detect that columns 2 is always unique against column 1.

for example

when new data inserted

table A
id = 1 (identity)
column 1 = 5
column 2 = 2 (foreign key)
...
...
...

next insertion
table A
id = 2
column 1 = 6
column 2 = 2
...
...
...

next insertion
table A
id = 3
column 1 = 5
column 2 = 2
...
...
will trigger error...because redundant data inserted. previous insert at id = 1.

please help on how to setup the table. FYI I'm using SQL Server 2005 Express

Is This A Good Question/Topic? 0
  • +

Replies To: how to detect two columns (not primary key) redundant data insertion

#2 Duckington  Icon User is offline

  • D.I.C Addict

Reputation: 164
  • View blog
  • Posts: 599
  • Joined: 12-October 09

Re: how to detect two columns (not primary key) redundant data insertion

Posted 27 June 2012 - 01:41 AM

One way you could do it is to create a UNIQUE index on those two columns, e.g:

ALTER TABLE `yourtable` ADD UNIQUE `myUniqueIndex` ( `column1` , `column2` )



Then if you try to insert data into those columns which already exists, you will get an SQL error:

Quote

#1062 - Duplicate entry 'x-y' for key 'myUniqueIndex'


Edit: Not entirely sure how to do it in SQL Server...have a look at this, may help.

This post has been edited by Duckington: 27 June 2012 - 03:41 AM

Was This Post Helpful? 1
  • +
  • -

#3 arrezes  Icon User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 58
  • Joined: 17-October 10

Re: how to detect two columns (not primary key) redundant data insertion

Posted 27 June 2012 - 02:15 AM

ALTER TABLE `yourtable` ADD UNIQUE `myUniqueIndex` ( `column1` , `column1` )



the 'myUniqueIndex' is just a naming right? nothing relates to my table
Was This Post Helpful? 0
  • +
  • -

#4 Duckington  Icon User is offline

  • D.I.C Addict

Reputation: 164
  • View blog
  • Posts: 599
  • Joined: 12-October 09

Re: how to detect two columns (not primary key) redundant data insertion

Posted 27 June 2012 - 02:41 AM

THta's just the name I gave the index, yes.
Was This Post Helpful? 1
  • +
  • -

#5 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 782
  • View blog
  • Posts: 1,663
  • Joined: 30-January 09

Re: how to detect two columns (not primary key) redundant data insertion

Posted 27 June 2012 - 02:59 AM

Make sure you use the two columns though...
ALTER TABLE `yourtable` ADD UNIQUE `myUniqueIndex` ( `column1` , `column2` )


Was This Post Helpful? 0
  • +
  • -

#6 Duckington  Icon User is offline

  • D.I.C Addict

Reputation: 164
  • View blog
  • Posts: 599
  • Joined: 12-October 09

Re: how to detect two columns (not primary key) redundant data insertion

Posted 27 June 2012 - 03:41 AM

Yeah that should have said column2, fixed it now.
Was This Post Helpful? 0
  • +
  • -

#7 arrezes  Icon User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 58
  • Joined: 17-October 10

Re: how to detect two columns (not primary key) redundant data insertion

Posted 27 June 2012 - 07:38 PM

View Poste_i_pi, on 27 June 2012 - 02:59 AM, said:

Make sure you use the two columns though...


so sorry what do you mean by that? :sweatdrop:
Was This Post Helpful? 0
  • +
  • -

#8 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 782
  • View blog
  • Posts: 1,663
  • Joined: 30-January 09

Re: how to detect two columns (not primary key) redundant data insertion

Posted 27 June 2012 - 07:57 PM

Duckington originally had this:
ALTER TABLE `yourtable` ADD UNIQUE `myUniqueIndex` ( `column1` , `column1` )


...but it should have been this...
ALTER TABLE `yourtable` ADD UNIQUE `myUniqueIndex` ( `column1` , `column2` )


Was This Post Helpful? 1
  • +
  • -

#9 arrezes  Icon User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 58
  • Joined: 17-October 10

Re: how to detect two columns (not primary key) redundant data insertion

Posted 27 June 2012 - 08:08 PM

owww....didn't notice that. from you first post at a glimpse I knew you meant ('column1', 'column2')

that's what i put in my query....thanks guys for your help
Was This Post Helpful? 0
  • +
  • -

#10 arrezes  Icon User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 58
  • Joined: 17-October 10

Re: how to detect two columns (not primary key) redundant data insertion

Posted 27 June 2012 - 11:52 PM

I'm getting error.

My query looks like this
ALTER TABLE jobcard ADD column1 NVACHAR(50), column2 INT UNIQUE



This query worked, but this didn't

ALTER TABLE jobcard ADD jobcard_no NVACHAR(50), workorder_id INT UNIQUE



Attached Image

referring my very first post in this thread, workorder_id can have multiple of unique jobcard_no. However when manually inserting the data I am still getting error as attached pics.

This post has been edited by arrezes: 27 June 2012 - 11:54 PM

Was This Post Helpful? 0
  • +
  • -

#11 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 782
  • View blog
  • Posts: 1,663
  • Joined: 30-January 09

Re: how to detect two columns (not primary key) redundant data insertion

Posted 28 June 2012 - 03:25 AM

Woops, try this:
ALTER TABLE yourtable ADD CONSTRAINT myUniqueIndex UNIQUE NONCLUSTERED ( column1 , column2 )


I think that's the MSSQL syntax, I can double check when I get to work tomorrow.
Was This Post Helpful? 1
  • +
  • -

#12 arrezes  Icon User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 58
  • Joined: 17-October 10

Re: how to detect two columns (not primary key) redundant data insertion

Posted 28 June 2012 - 06:44 PM

View Poste_i_pi, on 28 June 2012 - 03:25 AM, said:

ALTER TABLE yourtable ADD CONSTRAINT myUniqueIndex UNIQUE NONCLUSTERED ( column1 , column2 )



This query worked but still get error when I have the same jobcard_no and the workorder_id is having different number.

the pseudo goes like this
if ((jobcard_no == prevJobcard_no) && (workorder_id == prevWorkorder_id))
    error
else if ((jobcard_no == prevJobcard_no) && (workorder_id != prevWorkorder_id))
    ok
else if ((jobcard_no != prevJobcard_no) && (workorder_id == prevWorkorder_id))
    ok
else
    ok (when both are not same)



thanks
Was This Post Helpful? 0
  • +
  • -

#13 arrezes  Icon User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 58
  • Joined: 17-October 10

Re: how to detect two columns (not primary key) redundant data insertion

Posted 28 June 2012 - 07:09 PM

it's working...

I used back the first query Duckington gave me.

ALTER TABLE jobcard ADD UNIQUE indexedJCWO ( jobcard_no , workorder_id )



tried manually enter the record and tested all possibilities. and it worked

thank you so much :^:
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1