0

How to set constraint sql 2 colume, Data is not available elsewhere and must be used only. Example is OK

NAME REV ID PI
A 0 1 PA001
A 1 1 PA001

Example is Error

NAME REV ID PI
A 0 1 PA001
A 1 1 PA002

When ID = 1 then PI is PA001 Only

We can't insert Any data to table except PA001 when ID = 1 enter image description here

2

The requirement.is unclear. More examples/better specification is needed..

However, i asume that after an ID is " added " to PI, this becomes " paired" and can't be paired with different ID/PI.

  1. create a pairing table (ID,PI) with PK on Id and Unique on PI. Then add FK from your table to this new table either on (PI,ID) or an identity column

  2. There is no simple constraint that would do what you need. What can be used is a CHECK constraint with an function that would do the check by counting ID/PI pairs with same halves. However this is not very optimal when it comes to performance

1
0

Add according CHECK constraint:

CREATE TABLE {tablename} ( {table definition},
                           CHECK (ID != 1 OR PI = 'PA001') )

CREATE TABLE test (NAME CHAR(1), REV INT, ID INT, PI CHAR(5),
                   CHECK (ID != 1 OR PI = 'PA001'));
GO
INSERT INTO test VALUES ('A', 0, 1, 'PA001');
GO
INSERT INTO test VALUES ('A', 1, 1, 'PA001');
GO
INSERT INTO test VALUES ('A', 2, 1, 'PA002');
GO
Msg 547 Level 16 State 0 Line 1
The INSERT statement conflicted with the CHECK constraint "CK__test__35BCFE0A". The conflict occurred in database "fiddle_c4abb5f9c518435fb6f88f2215747a89", table "dbo.test".
Msg 3621 Level 0 State 0 Line 1
The statement has been terminated.

db<>fiddle here


that table and data is example ID has any data when match data so pi, not any value exam id 1 PI = PA001, id 2 PI = PA002, id 999 PI = PA011 – พิมพ์ชดารัตน์ เนตรวานิช

Alter CHECK constraint accordingly.

CHECK (PI = 'PA' + FORMAT(ID, '000'))

https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=958a54cb5dcd060a54561bd09105eabf

4
  • thank you for your Answers. that table and data is example ID has any data when match data so pi, not any value exam id 1 PI = PA001, id 2 PI = PA002, id 999 PI = PA011 – พิมพ์ชดารัตน์ เนตรวานิช Apr 20 at 5:43
  • @พิมพ์ชดารัตน์เนตรวานิช Alter CHECK constraint accordingly. id 999 PI = PA011 Maybe, PA999? Answer updated. – Akina Apr 20 at 6:02
  • I am afraid this answer takes the example too litarrary and tries to find a pattern between ID and PI where there is no pattern – Vladislav Zalesak Apr 20 at 8:48
  • @VladislavZalesak This is the author's requirement. I do not discuss the correctness of his requirement. True, I'd prefer generated column instead of checked one too - but this isn't my business. – Akina Apr 20 at 8:52
0

Thank you for your answers. Now I created table test for test function check

CREATE FUNCTION [dbo].FN_UNQ_PI
RETURNS INT
AS
BEGIN
DECLARE @check INT
SELECT @check = COUNT(DISTINCT PI) FROM dbo.TEST GROUP BY PI RETURN @check END; CREATE FUNCTION [dbo].FN_UNQ_ID
RETURNS INT
AS
BEGIN
DECLARE @check INT
SELECT @check = COUNT(DISTINCT ID) FROM dbo.TEST GROUP BY ID RETURN @check END;

and I set CONSTRAINT

ALTER TABLE [dbo].[TEST] WITH CHECK ADD CONSTRAINT [CK_UNQ] CHECK (([dbo].FN_UNQ_PI=(1) and ([dbo].FN_UNQ_ID=(1) ))

when test on table test is ok but insert to Active table (The actual data is about 200000 rows) it can't execute (An not error but is a long time for executing and not completed) i recheck DISTINCT PI and ID (group by PI and ID) this all group DISTINCT = 1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.