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

Question

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

Answer 1

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

Answer 2

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

Answer 3

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