[arrezes]

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

[Duckington]

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

[arrezes]

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

the 'myUniqueIndex' is just a naming right? nothing relates to my table

[Duckington]

THta's just the name I gave the index, yes.

[e_i_pi]

Make sure you use the two columns though...

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

[Duckington]

Yeah that should have said column2, fixed it now.

[arrezes]

e_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?

[e_i_pi]

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` )

[arrezes]

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

[arrezes]

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

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

[e_i_pi]

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.

[arrezes]

e_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

[arrezes]

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