Constraint in a column

Question

Constraint in a column

haichells

SSCrazy

Points: 2713
More actions
April 29, 2020 at 4:06 am

#3747207

Hi,
I have a requirement where my table (ex: TableA having columns Tranno , Trandate, Itemno). Tranno can be repeated in this table as it may have multiple Itemno for one Tranno. But Trandate needs to be only one value for one particular TranNo. Here i need to build a constraint such that for one TranNo can have one Trandate even if there are multiple rows available. Can you please help me to build a constraint or any easy way to restrict the wrong data.

Thanks in Advance

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

chris.johnson.0120 SSC-Addicted Points: 491 More actions · Answer 1

It sounds to me like you actually want to normalise your data a bit.

So, split TableA into Transaction and Item, where Transaction holds TransactionNo, TransactionDate, and Item holds ItemNo and TransactionNo as a foreign key to Transaction.

elric.sims SSC Rookie Points: 29 More actions · Answer 2

You want to normalize your data...

However... there is a way you can do this...

Before I tell you how... know that this type of solution is more ticks in the grand scheme of things. thus... a super slow and not very scalable solution, but it answers your question/request.

you can create a simple function like:

If there are previous records with the Tranno, this function will check the date before it lets the insert pass.

if there isnt, (NULL @PreviousDate) it will allow this record, being the first record for Tranno.

IF there is a previous date, and they dont match, it will FAIL.

CREATE FUNCTION dbo.fnc_CheckConstraint_TransactionDate
(

@TransactionIdBIGINT
, @TransactionDate DATETIME2(7)
)
RETURNS BIT
AS
BEGIN

DECLARE @DateMatchesFlagBIT
DECLARE @PreviousDateDATETIME2(7)

SELECT @PreviousDate = TranDate
FROM TableA
WHERE Tranno = @TransactionId

IF @PreviousDate IS NULL
BEGIN
        SELECT @DateMatchesFlag = 1
        END
ELSE 
BEGIN  
         IF @PreviousDate = @TransactionDate
BEGIN
                SELECT @DateMatchesFlag = 1
                END
ELSE
BEGIN
                SELECT @DateMatchesFlag = 0
                END
        END

RETURN @DateMatchesFlag

END

And something like this

ALTER TABLE TableA
WITH CHECK ADD CONSTRAINT CK_TransactionDateUnification
CHECK (dbo.fnc_CheckConstraint_TransactionDate(Tranno, Trandate) =1)

That is the constraint version.

The other version is an AFTER INSERT, UPDATE trigger, or INSTEAD OF. But that is not a constraint, that is a hack that momentarily lets the bad data into the system.

MCSA, Data Architect
SQL Master Data Management

https://youtube.com/elricsims-dataarchitect

9001st Normal Form

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 3

I agree with the others... you need two tables. One for the transaction HEADER and one for the transaction DETAIL.

I'll also add that even though some folks may provide a workable solution for you that solves your exact problem, don't do it that way. You will hate yourself for years to come.

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

jcelko212 32090 SSCrazy Eights Points: 9303 More actions · Answer 4

You need to get a book on basic relational databases. Look up normalization and in particular, pay attention to second and third normal forms.

You also need to pay attention to SQL forum rules and basic netiquette. Why did you failed to post any DDL?

CREATE TABLE Transactions
(transaction_number CHAR(16) NOT NULL PRIMARY KEY,
transaction_date DATE DEFAULT CURRENT_TIMESTAMP);

CREATE TABLE Transaction_Details
(transaction_number CHAR(16) NOT NULL
REFERENCES Transactions(transaction_number)
ON DELETE CASCADE,
item_gtin CHAR(15) NOT NULL,
PRIMARY KEY (transaction_number, item_gtin));

Pay attention to how the REFERENCES clauses relate the two tables and how the DRI actions maintain the data integrity.

Please post DDL and follow ANSI/ISO standards when asking for help.