How to add a constraint which refers to a different table in SQL Server?-CodePudding

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?

CodePudding user response：

i always do these kind of limitations on 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)

CodePudding user response：

You can have a relation constraint or value constraint. It is impossible to have both on one constraint. For 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 cant 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.