I think this is a Check Constraint question.

Question

Post reply

I think this is a Check Constraint question.

jameslauf

SSC Eights!

Points: 928
More actions
September 23, 2015 at 1:02 pm

#308023

I am trying to setup a constraint on a 2012 database the allows only one "LineitemNumber" for each InvoiceID. I am not certain how to do this. Both of these columns exist in the same table.
I know the starting code would look like: ALTER TABLE dbo.InvoiceLineItemBase WITH NOCHECK
ADD CONSTRAINT CK_LineItemNumber
I appreciate any and all help.
Thanks
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

mister.magoo SSC-Forever Points: 47174 More actions · Answer 1

I would use a unique index on InvoiceID filtered where LineitemNumber is not null;

This way you can have as many Null LineitemNumber rows as you like for each InvoiceID, but only one NON-NULL value

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

You can also define the columns as the Primary Key.

ALTER TABLE OrderDetails

ADD CONSTRAINT PK_OrderDetails PRIMARY KEY (InvoiceID, LineitemNumber);

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

mister.magoo SSC-Forever Points: 47174 More actions · Answer 3

Luis Cazares (9/23/2015)
You can also define the columns as the Primary Key.
ALTER TABLE OrderDetails
ADD CONSTRAINT PK_OrderDetails PRIMARY KEY (InvoiceID, LineitemNumber);

Yes, I read the question as multiple rows per InvoiceID, with only one allowed to have a LineItemNumber value, but if the intention is to have multiple rows per InvoiceID with multiple different LineItemNumbers, then this is the way to go.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

jameslauf SSC Eights! Points: 928 More actions · Answer 4

I am sorry I re-Read my original post and i was unclear.

I want individual LineitemNumbers per each InvoiceId.

so I cna have Invoice#97865 with LineItemNumbers 1,2,3.

right now I am getting Invoice#97865 with LineItemNumbers of 1,2,2,3,4,4,5 etc.

mister.magoo SSC-Forever Points: 47174 More actions · Answer 5

jameslauf (9/23/2015)
I am sorry I re-Read my original post and i was unclear.
I want individual LineitemNumbers per each InvoiceId.
so I cna have Invoice#97865 with LineItemNumbers 1,2,3.
right now I am getting Invoice#97865 with LineItemNumbers of 1,2,2,3,4,4,5 etc.

Well, you can enforce the uniqueness with a primary key like Luis suggested, but if you are already seeing duplicates, then adding the PK will cause errors to bubble up in the application that is inserting the rows, so be prepared...

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

WayneS SSC Guru Points: 95460 More actions · Answer 6

If you've already got a primary key on this table, you can also use a unique index.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 7

jameslauf (9/23/2015)
I am sorry I re-Read my original post and i was unclear.
I want individual LineitemNumbers per each InvoiceId.
so I cna have Invoice#97865 with LineItemNumbers 1,2,3.
right now I am getting Invoice#97865 with LineItemNumbers of 1,2,2,3,4,4,5 etc.

That's actually a display function, IMHO. What would such a sequence provide other than just for display purposes?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)