How to add a constraint which refers to a different table

Question

Let's assume I have the tables Job (JobPK INT etc.) and Employee (EmployeePK INT, Age INT etc.).

I want to create another table A, defined as:

CREATE TABLE A
(
   JobFK INT,
   EmployeeFK INT,
   CONSTRAINT FOREIGN KEY (JobFK) REFERENCES Job(JobPK),
   CONSTRAINT FOREIGN KEY (EmployeeFK) REFERENCES Employee(EmployeePK)
)

where I can add only those employees whose age is greater than 25, for example.

How can I do that?

Note: Storing age is almost always a bad plan. Because you are *guaranteed* that on some unknown date in the next 366 days, it will become inaccurate. Storing Date of Birth and *deriving* age is usually far more sensible. — Damien_The_Unbeliever, Oct 19 '21 at 07:20
Does this answer your question? [Adding constraints that check a separate (linked) table for a value](https://stackoverflow.com/questions/65825778/adding-constraints-that-check-a-separate-linked-table-for-a-value) — Charlieface, Oct 19 '21 at 09:30

score 0 · Answer 1 · edited Oct 19 '21 at 07:18

0

I always implement these kind of limitations in the UI level (some validation plugins).

In SQL Server CHECK can be used

ALTER TABLE Employee  ADD CONSTRAINT CK_AGE
    CHECK (Age < 80 AND Age > 25)

edited Oct 19 '21 at 07:18

Dale K

25,246
15
42
71

answered Oct 19 '21 at 06:44

Ali Fidanli

1,342
8
12

score 0 · Answer 2 · edited Oct 19 '21 at 07:17

You can have a relation constraint or value constraint. It is impossible to have both on one constraint.

For a value constraint, you can define it in creating the table or add it later with ADD CONSTRAINT.

For example:

CREATE TABLE Employee (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int CHECK (Age>=25)
);

You can't have a relation constraint that ensures just employees with an age greater than 25. relation constraint always applies on all records not a subset of it.

score 0 · Answer 3 · edited Oct 19 '21 at 07:41

0

alter table tablename 
add fieldname datatype check(conditional expression);

Example :

alter table A
add age smalint check(age >= 25);

edited Oct 19 '21 at 07:41

desertnaut

57,590
26
140
166

answered Oct 19 '21 at 07:38

Shivaraj M

1
1

1

Please do not spam (link removed). – desertnaut Oct 19 '21 at 07:41

score 0 · Answer 4 · answered Oct 19 '21 at 07:48

Preferably do it at application level. Nevertheless you can implement it as a DB constraint

CREATE TABLE Employee
(
   EmployeePK INT identity(1,1) ,
   Age int,
   CONSTRAINT pk0 PRIMARY KEY (EmployeePK),
   CONSTRAINT uk1 UNIQUE (EmployeePK, Age)
);

and

CREATE TABLE A
(
   JobFK INT,
   EmployeeFK INT,
   EmployeeAge INT CHECK (EmployeeAge >=25),
   CONSTRAINT fk1 FOREIGN KEY (EmployeeFK) REFERENCES Employee(EmployeePK),
   CONSTRAINT fk2 FOREIGN KEY (EmployeeFK, EmployeeAge) REFERENCES Employee(EmployeePK, Age) ON UPDATE CASCADE
);

Insert test data

insert Employee
values (30),(20);
insert A
values (100,1,30);

This queries will fail:

insert A
values (100,2,20);

update Employee 
set age = age-10
where age > 25;

How to add a constraint which refers to a different table

4 Answers4