Unique constraint on two fields two tables...

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Unique constraint on two fields two tables...

Author

Topic

cidr
Posting Yak Master

207 Posts

Posted - 2008-08-25 : 06:03:55

Hi There.

I hope someone can help.

I'm working on a db for complaints.

I have two table that are directly related. The first table (ComplaintNumber) will list the Complaint number for each complainant. Because each complaint can have three stages, or escalations, the second table (ComplaintDetails) has a field called StageNumber.

So, one complaint can have stage 1,2,3, on the other table which has detail of each complaint. all the details for each stage will be different (dates, Investagotors etc) on the ComplaintDetail table.

If the complaint Number is 1, the setup may look like this in a query:

Complaint 1 Stage 1
Complaint 1 Stage 2
Complaint 1 Stage 3

I want to be able to create a unique constraint between the two fields so that this wouldn't happen:

Complaint 1 Stage 1
Complaint 1 Stage 1

I'm using an MS Access frontend and it's all automated with VBA. I've tested it and it's quite efficient but I want to make sure that the latter example never happens and therefore want a unique constraint on the two fields (tables).

Is this possible?

Thanks in advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-25 : 06:07:46

yes its possible you can created a composite unique key on the two columns.

cidr
Posting Yak Master

207 Posts

Posted - 2008-08-25 : 08:22:14

Thanks for getting back to me visakh.

I'm a little confused. The columns are in two seperate tables. I know I can create a composite key on two fields within the same table. How would I do it with two fields from different tables?

Paul

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-25 : 08:23:42

quote:
Originally posted by cidr

Thanks for getting back to me visakh.

I'm a little confused. The columns are in two seperate tables. I know I can create a composite key on two fields within the same table. How would I do it with two fields from different tables?

Paul

Ah ...are they in two different tables? But wont detail table be having ComplaintId field?

cidr
Posting Yak Master

207 Posts

Posted - 2008-08-26 : 05:06:25

Sorry visakh, I should have given more info on the layout.

The complaintNoID is in the ComplaintDetail table and its the FK so the values are unique. The complaintNumber field has repeating values depending on the number of complaints made by a person
and this is only found in the ComplaintNumber table along with the complaintNoID as the PK

I'm not very good at representing this but here goes.

On the ComplaintNumber table are the relevant values:

ComplaintNoID, PK, Int
PersonID, FK, int
ComplaintNumber, Int *

The ComplaintDetail table:
ComplaintID, PK, Int
ComplaintNoID, FK, Int
StageNumber, Int *

On a query it would look like this:

ComplaintNoID StageNumber ComplaintNumber
1....................1...................1
2....................2...................1
3....................3...................1
4....................1...................2
5....................2...................2 And so on...

I want to try and create some kind of unique constraint between StageNumber from table ComplaintDetail and ComplaintNumber from table ComplaintNumber so that each of these values cannot be duplicated togeter.

Is that possible?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-26 : 05:22:16

You've both of the fields in ComplaintDetail table. SO cant you create unique constraint on both of them?

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2008-08-26 : 05:28:34

I am not sure if you can create constraint for ComplaintNumber column in ComplaintNumber table and
StageNumber column in ComplaintDetail table. I feel a trigger on ComplaintNumber table is best suited for your requirement i.e. not getting these values duplicated.

cidr
Posting Yak Master

207 Posts

Posted - 2008-08-26 : 06:16:07

visakh wrote:

quote:

You've both of the fields in ComplaintDetail table. SO cant you create unique constraint on both of them?

I don't have both the fields in the same table, I have ComplaintNoID and StageNumber within the complaintDetail table, the ComplaintNoID is an incrementel PK of the other table. It's the ComplaintNumber from the ComplaintNumber table and the StageNumber from the ComplaintDetail that, together, have to be unique. I'm not trying to create an index for the ComplaintNoID and the StageNumber fields.

If I was to place an unique composite index on the ComplaintNoID FK and the StageNumber of the ComplaintDetail table, of Course they'd be unique because there's only one of a kind with the FK.

On the other hand, the ComplaintNumber field on the other table can have duplicate values because there can be several complaints for each person, each complaint number can have three stages,1,2,3

sunil wrote:

quote:

I am not sure if you can create constraint for ComplaintNumber column in ComplaintNumber table and
StageNumber column in ComplaintDetail table

I thought this might be the case, I just wanted to double check with you guys.

I'll have a look at what I could do with triggers. thanks to both

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-26 : 06:54:40

Ok. i mistokk seeing the sample result that you've both fields. Yup i think you can use trigger in that case as Sunil suggested.

cidr
Posting Yak Master

207 Posts

Posted - 2008-08-27 : 05:16:02

Thanks for the help folks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources