Adding Constraints That Check a Separate (Linked) Table for a Value

Adding constraints that check a separate (linked) table for a value

If you'll always have a single status to check, this can be done with little tricks on FK constraint:

• Create dummy unuque index on Books(BookId, Decision).
• Add calculated column to BookShipment with value Approved.
• Reference the created unique index in FK constraint.

Defining UDF in CHECK constraint should be more flexible way for this.

create table book (
  BookID int identity(1,1) primary key,
  Title varchar(100),
  Author varchar(100),
  Decision varchar(100),
  
  --Dummy constraint for FK
  constraint u_book unique(bookid, decision)
);

CREATE TABLE BookShipment(
  BookID int,
  ShipmentID varchar(7),
  --Dummy column for FK
  approved as cast('Approved' as varchar(100)) persisted
  
  CONSTRAINT pk_BookShipment PRIMARY KEY (BookID),
  CONSTRAINT fk_BookShipment_Book_Approved
    FOREIGN KEY (BookID, approved)
    REFERENCES Book(BookID, decision)
);

insert into book (Title, Author, Decision)
select 'A', 'B', 'Approved' union all
select 'A', 'B', 'New'
;

--2 rows affected

insert into BookShipment values(1, 1);

--1 rows affected

insert into BookShipment values(2, 2);

/*

insert into BookShipment values(2, 2);

Msg 547 Level 16 State 0 Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint "fk_BookShipment_Book_Approved". The conflict occurred in database "fiddle_ea408f09b06247a78b47ea9c353eda10", table "dbo.book".
Msg 3621 Level 0 State 0 Line 1
The statement has been terminated.
*/

db<>fiddle here

Use check constraint on a column based on the value of a column in another table

A check constraint can't reference other tables, so you can achieve what you want only by using 2 separate triggers for INSERT and UPDATE:

CREATE TRIGGER insert_member_number BEFORE INSERT ON members
BEGIN
  SELECT
    CASE
      WHEN NEW.member_number NOT BETWEEN 1 AND (SELECT member_count FROM teams WHERE id = NEW.team_id) 
        THEN RAISE (ABORT, 'Invalid member number')
    END;
END;

CREATE TRIGGER update_member_number BEFORE UPDATE ON members
BEGIN
  SELECT
    CASE
      WHEN NEW.member_number NOT BETWEEN 1 AND (SELECT member_count FROM teams WHERE id = NEW.team_id) 
        THEN RAISE (ABORT, 'Invalid member number')
    END;
END;

Also, I believe you should change the definition of the table members so that the combination of the columns team_id and member_number is UNIQUE:

CREATE TABLE members (
    id INTEGER PRIMARY KEY,
    team_id INTEGER NOT NULL,
    member_number INTEGER NOT NULL,
    UNIQUE(team_id, member_number),
    FOREIGN KEY (team_id) REFERENCES teams (id)
);

See the demo.

How do I create a constraint in a junction table that disallows null values from parent tables?

The answer to your trigger problem is the perennial issue we see with triggers: no join on inserted and deleted pseudo-tables.

Furthermore, when you rollback in a trigger, you get an error "Transaction ended in the trigger", which isn't very user-friendly. So instead use THROW and rely on the server rolling back the implicit transaction:

CREATE TRIGGER Trg_Employee_Shift_INS  -- give proper trigger names
ON EMPLOYEE_SHIFT
AFTER INSERT, UPDATE    -- we can do this for updates too
AS

SET NOCOUNT, XACT_ABORT ON;  -- forces a rollback

IF NOT EXISTS (select 1
    from EMPLOYEE e
    inner join inserted i ON i.eid = e.EID 
    where e.MANAGER IS NOT NULL)
BEGIN
    THROW 50000, 'THIS EMPLOYEE DOESN'T HAVE A MANAGER', 0;
END;

You are probably going to want a trigger on the Employee table too.

---

Instead of all this, we can use a trick involving Indexed Views, that I picked up from spaghettidba.

I have written a fuller explanation of how it works in this post, but I will show you how to use it in your case.

Let's start by creating a dummy table, with exactly two rows in it:

CREATE TABLE dbo.DummyTwoRows (x bit NOT NULL PRIMARY KEY);
GO
INSERT dbo.DummyTwoRows VALUES (0),(1);

Now, we create a view that selects all rows that fail the condition we would like to enforce. We cross-join it with the above table, to get two rows for every failing row:

CREATE /* OR ALTER */ VIEW dbo.vwShiftEmployeesNoManagers
WITH SCHEMABINDING    -- must be schema-bound, so can't change underlying columns
AS
SELECT 1 AS DummyOne
FROM dbo.Employee e    -- must use two-part names
JOIN dbo.Employee_Shift es ON es.eID = e.eID
CROSS JOIN dbo.DummyTwoRows
WHERE e.Manager IS NULL;

Now this is the magic:

When we create a clustered index on the view, SQL Server will maintain it. So every row inserted or updated in the base tables will pass through the above join, so that the view can be materialized on disk.

Since every row gets doubled via the cross join, a unique constraint will fail if any row passes the WHERE filter.

So we create an index:

CREATE UNIQUE CLUSTERED INDEX CX_vwShiftEmployeesNoManagers
    ON dbo.vwShiftEmployeesNoManagers (DummyOne);

Note that since no rows can exist in the view, it will take up no space on disk. If your indexing is right, it should be cheap to maintain also.

---

Incidentally, your join table should probably not have it's own primary key. Instead, make up the primary key from the two foreign keys.

Having data from another table put in into check constraint

Unfortunately you can not insert Sub Query into context of Check constraint. But here I would like give suggestion, You can use any trigger or function , You can use
foreign key constraint to check data dependency
I would like to share one example with function.
e.g.

CREATE FUNCTION fn_Check_Rollnumber (
    @Rollnumber INT
)
RETURNS VARCHAR(10)
AS
BEGIN
    IF EXISTS (SELECT Rollnumber FROM Table_Student WHERE Rollnumber = @Rollnumber)
        return 'True'
    return 'False'
END

Now you can use this function in you Check context like,

ALTER TABLE Table_Fees 
    WITH CHECK ADD CONSTRAINT CK_RollCheck
    CHECK (fn_Check_Rollnumber(Rollnumber) = 'True')

How to add check constraint on a column in table?

The error message is a little badly worded, but isn't saying what you think it's saying.

It's not saying that there's already a constraint with the same name. It's saying that the constraint is being violated. That means that there is data already in the table that doesn't meet the requirements of the new constraint you're trying to introduce.

You could use the NOCHECK option to create the constraint whilst allowing existing data to violate it. But this is frequently the wrong thing to do. It is usually more sensible to fix the existing data.

Specifying NOCHECK means that the constraint can't be used by the optimizer to eliminate redundant actions that the logic of the constraint would preclude.

How do I have a check constraint that refers to another table?

Add a column tblItem.ItemType column. This column can have only one value on any given row (obviously). Add a unique constraint over ItemID,ItemType.

Now the trick: few people remember this, but a foreign key can reference the columns of a unique constraint.

CREATE TABLE tblItem (
  ItemID INT PRIMARY KEY,
  ItemType CHAR(1),
  UNIQUE KEY (ItemID, ItemType)
);

CREATE TABLE tblGoodItem (
  ItemID INT PRIMARY KEY,
  ItemType CHAR(1),
  CHECK (ItemType='G')
  FOREIGN KEY (ItemID, ItemType) REFERENCES tblItem(ItemID, ItemType) 
);

CREATE TABLE tblBadItem (
  ItemID INT PRIMARY KEY
  ItemType CHAR(1),
  CHECK (ItemType='B')
  FOREIGN KEY (ItemID, ItemType) REFERENCES tblItem(ItemID, ItemType) 
);

If you constrain ItemType in each of the child tables to a fixed value, then a given row in tblItem can be referenced by only one child table.

It's a three-step process to change an item from good to bad, though:

1. DELETE row from tblGoodItem
2. UPDATE row's ItemType in tblItem
3. INSERT row in tblBadItem

Ensure combination of two columns existing in another table

In addition to Sticky bit and Vladimir Baranov's answers about using FOREIGN KEY and REFERENCES, for sqlite3 users:

"Foreign key constraints are disabled by default (for backwards compatibility), so must be enabled separately for each database connection." That means, the user has to open a database first, then run command "PRAGMA foreign_keys = ON;".

The pragma will not work if "the version of SQLite you are using does not support foreign keys (either because it is older than 3.6.19 or because it was compiled with SQLITE_OMIT_FOREIGN_KEY or SQLITE_OMIT_TRIGGER defined)".

---

Related Topics