Can a Check Constraint Relate to Another Table

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 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

Can a check constraint relate to another table? Oracle

No it can't.

A FOREIGN KEY constraint can (and must) relate to another table, but it can only perform equiality checks.

I.e. you can test that a column (or a set of columns) are equal to those in the other table but not more complex conditions (like inside a span or whatever).

You'll have to implement a trigger for that.

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.

Check Constraint that references another table

I think the below is what you are expecting:

create a function that returns the values from album table:

Create function [dbo].[MaxValue]()
returns decimal
as
begin
   declare @retval int
   select @retval=MAX(album_itunes_price) from dbo.albums
   return @retval
end;

Then create check constraint in songs which can get the value from albums table, because we can't use subquery in check constraint.

alter table dbo.songs
add constraint ck_songs_itunes_cost 
check (songscolumn < dbo.MaxValue())

Make use of it based on your need.

SQL constraint check if entry in another table

The documentation says:

The expression of a CHECK constraint may not contain a subquery.

So you have to use triggers:

CREATE TRIGGER no_snowflake_if_special
AFTER INSERT ON snowflake
WHEN EXISTS (SELECT * FROM special WHERE id = NEW.id)
BEGIN
    SELECT RAISE(FAIL, "a special with the same ID already exists");
END;

-- same for special

SQL Constraint to check the value of another table's column

You can do what you are specifically asking for using foreign keys and computed columns. First, define a redundant unique constraint in employees:

alter table employees add constraint unq_employees_empid_active_status on (empid, active_status);

Then, define a computed column in sales. Alas, this needs to be persisted, I think:

alter table sales add active_status as (convert(bit, 1)) persisted;

Then, define the foreign key constraint using both:

alter table sales add foreign key fk_sales_employees_active
    foreign key (empid, active_status) references employees(empid, active_status);

Voila! The employee id can only reference active employees.

Now, you will have a problem with this -- be careful what you ask for. It is not really what you want. This enforces the constraint over all time. So, you won't be able to change the status on employees who have sales. That suggests that you need an insert trigger instead -- or a user defined function and check constraint:

create function is_employee_active (
    @empid int
) returns bit as
begin
    return (select active_status from employees e where e.empid = @empid);
end;

alter table sales add constraint chk_sales_employee_active
    check (is_employee_active(empid) = convert(bit, 1));

Voila! This only does the check on insertion or updates. Note that once an employee is not active, you won't be able to update the row either.

You'll notice that I usually name my tables in the plural, because they contain lots of examples of an entity. My fingers just add the "s" when I'm thinking about tables.

Check Constraint Referencing Unique Column on another Table

First, like GordonLinoff comments, the better approach is to include TitleID in the Customer table. Below is an option if you can't change the layout of the Customer table. A foreign key is definitely better than using dynamic T-SQL to keep a check constraint up to date.

I cannot reference the title.label column with a foreign key as it is
not a primary key.

A foreign key can reference any candidate key. It doesn't have to reference the primary key.

To tell the database about candidate keys, you can create a unique index:

create table title (
    id int primary key, 
    label varchar(50));
create table customer (
    id int primary key, 
    title varchar(50));
create unique index ux_title_label on title(label);
alter table customer add constraint fk_customer_title 
    foreign key (title) references title(label);

Another way to tell the database about a candidate key is a unique constraint:

alter table title add constraint uc_title_label unique (label);

---

Related Topics