January 22, 2014 at 7:22 am
Is it possible to have a constarint between two tables based on a FK relationship and a value based on another column. For example to have a valid record in table b the TableA_ID value needs to exist in tableA and the charge Value can't be null. So row number 3 would be invalid in table B in this example.
TableA
IDCharge_Value
1100
2Null
34
4Null
TableB
TableB_IDTableA_IDSome other data
1 1 A
2 3 B
3 4 C
January 22, 2014 at 7:29 am
I think you'd need a trigger to implement that sort of logic.
John
January 22, 2014 at 7:29 am
yes it's possible; you can create a CHECK constraint that uses a user defined function, that say returns 1 or 0 if the criteria is valid or not.
with DDL and sample data , we could offer an actual tested solution;
a similar solution can be found in this post: http://qa.sqlservercentral.com/Forums/Topic789104-145-1.aspx
in that case, a user defined function is used to make sure only one of two possible foreign keys are used;
your issue is very similar to that one.
Lowell
January 22, 2014 at 8:29 am
The function was the way to go,
CREATE FUNCTION fn_GetHeroPID(@id int)
RETURNS int
AS
BEGIN
RETURN (SELECT isnull(Charge_Value,0) FROM Table_A WHERE TableA_ID = @id)
END
GO
ALTER TABLE [TableA] WITH NOCHECK ADD CONSTRAINT MyCheck
CHECK(dbo.Fn_CheckValue(TableA_ID) <> 0 )
Thanks a lot for the help.
January 22, 2014 at 9:35 am
What do you expect to happen if we update TableA and set column [Charge_Value] to NULL mark for an existing row being referenced already from TableB?
If you do not want a row in TableB to reference a row in TableA where [Charge_Value] is the NULL mark then the function will not be enough.
CREATE TABLE dbo.TableA (
ID int NOT NULL PRIMARY KEY,
Charge_Value int NULL
);
GO
INSERT INTO dbo.TableA (
ID,
Charge_Value
)
VALUES
(1, 100),
(2, NULL),
(3, 4),
(4, NULL);
GO
CREATE FUNCTION dbo.ufn_CheckValue(@id int)
RETURNS int
AS
BEGIN
RETURN (SELECT ISNULL(Charge_Value,0) FROM dbo.TableA WHERE ID = @id)
END
GO
CREATE TABLE dbo.TableB (
TableB_ID int,
TableA_ID int NOT NULL,
c1 char(1) NOT NULL
);
GO
ALTER TABLE dbo.TableB WITH NOCHECK ADD CONSTRAINT MyCheck
CHECK(dbo.ufn_CheckValue(TableA_ID) <> 0 );
GO
INSERT INTO dbo.TableB (
TableB_ID,
TableA_ID,
c1
)
VALUES
(1, 1, 'A'),
(2, 3, 'B');
GO
-- fail as expected
INSERT INTO dbo.TableB (
TableB_ID,
TableA_ID,
c1
)
VALUES
(3, 4, 'C');
GO
UPDATE dbo.TableA -- <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< is this ok?
SET Charge_Value = NULL
WHERE ID = 1;
GO
SELECT *
FROM dbo.TableA;
GO
SELECT *
FROM dbo.TableB;
GO
DROP TABLE dbo.TableB;
GO
DROP TABLE dbo.TableA;
GO
DROP FUNCTION dbo.ufn_CheckValue;
GO
January 22, 2014 at 11:47 am
I am still waiting for your answer to see if updating a row in [TableA] and setting [Change_Value] to NULL mark is important in this context to avoid a row from [TableB] referencing a row from [TableA] that is being updating to have NULL mark in [Change_Value].
My suggestion will be to alter [TableA] and add a persisted computed column to hold 1 if [Change_Value] is NULL or zero otherwise. Then create a unique constraint by (ID, Change_Value_is_null).
Alter [TableB] and add a persisted computed column as zero value and add a foreign key constraint by (TableA_ID, Change_Value_is_null) referencing TableA(ID, Change_Value_is_null).
This will vslidate that you can't add a row to [TableB] referencing a row in [TableA] having [Change_Value] as NULL mark, netiher will allow to update a row in [TableA] and set [Change_Value] from any value to NULL mark.
SET NOCOUNT ON;
GO
CREATE TABLE dbo.TableA (
ID int NOT NULL PRIMARY KEY,
Charge_Value int NULL,
Charge_Value_is_null AS CASE WHEN Charge_Value IS NULL THEN 1 ELSE 0 END PERSISTED,
UNIQUE (ID, Charge_Value_is_null)
);
GO
INSERT INTO dbo.TableA (
ID,
Charge_Value
)
VALUES
(1, 100),
(2, NULL),
(3, 4),
(4, NULL);
GO
CREATE TABLE dbo.TableB (
TableB_ID int,
TableA_ID int NOT NULL,
c1 char(1) NOT NULL,
Charge_Value_is_null AS CAST(0 AS int) PERSISTED,
FOREIGN KEY (TableA_ID, Charge_Value_is_null) REFERENCES dbo.TableA(ID, Charge_Value_is_null)
);
GO
-- ok
INSERT INTO dbo.TableB (
TableB_ID,
TableA_ID,
c1
)
VALUES
(1, 1, 'A'),
(2, 3, 'B');
GO
-- fail as expected
INSERT INTO dbo.TableB (
TableB_ID,
TableA_ID,
c1
)
VALUES
(3, 4, 'C');
GO
-- fail as expected
UPDATE dbo.TableA
SET Charge_Value = NULL
WHERE ID = 1;
GO
SELECT *
FROM dbo.TableA;
GO
SELECT *
FROM dbo.TableB;
GO
DROP TABLE dbo.TableB;
GO
DROP TABLE dbo.TableA;
GO
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply