SQL server question

Question

SQL server question

ukhanjemb

Grasshopper

Points: 12
More actions
September 23, 2022 at 4:04 pm

#4091272

Hi,
I am just starting learning SQL..
I need help:
I want to create a check constraint for a column in Table1 so that it is smaller than another column value in another table?
I tried join but it is not working!

September 27, 2022 at 12:35 pm

This was removed by the editor as SPAM

Viewing 3 posts - 1 through 2 (of 2 total)

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

Ed B Hall of Fame Points: 3615 More actions · Answer 1

This is a rudimentary solution, but you can create a function that returns a 1 if the values meet the rule, and add a check constraint that the result of the function on the columns in question = 1.

This is a basic example, assuming the child table shares a unique identifier with the parent table to identify the values to compare. This is within the same database, but you can reference the parent table with three part naming. It may fail if the identifier column is not unique, but if that's the case you wouldn't know which row to compare. If the identifier doesn't exist in the parent table it is forced to fail. This acts as a pseudo-foreign key for the identifier column. If that is not what you want, then change the -1 to +1.

This doesn't stop you updating the parent table in a way that breaks the rule.


CREATE TABLE dbo.ParentScore
( RecordID INT, 
  Score INT)
GO

INSERT dbo.ParentScore
VALUES (1,100), (2,1000000)

GO

CREATE FUNCTION dbo.UdfScheckScore (@RecordID INT, @Score INT)
RETURNS INT AS
BEGIN
    DECLARE @Result INT
    SELECT @Result = CASE
                        WHEN ISNULL((  SELECT Score FROM dbo.ParentScore
                                WHERE RecordID = @RecordID),@Score-1) > @Score
                        THEN 1
                        ELSE 0
                    END 
    RETURN @Result

END

GO

SELECT dbo.UdfScheckScore (1,200)

SELECT dbo.UdfScheckScore (2,200)

GO

CREATE TABLE dbo.ChildScore
(   RecordID INT,
    Score INT,
    CONSTRAINT CKScore CHECK (dbo.UdfScheckScore (RecordID, Score) = 1)
)

GO

INSERT dbo.ChildScore
VALUES (1,99)

--The INSERT statement conflicted with the CHECK constraint "CKScore". The conflict occurred in database "TinyDB", table "dbo.OtherScore".
INSERT dbo.ChildScore
VALUES (1,100)

-- Fail because the score is null 
INSERT dbo.ChildScore
VALUES (3,0)

GO

ALTER TABLE  dbo.ChildScore DROP CONSTRAINT CKScore
DROP FUNCTION IF EXISTS dbo.UdfScheckScore
DROP TABLE IF EXISTS dbo.ChildScore
DROP TABLE IF EXISTS dbo.ParentScore
GO