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:
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.
CodePudding user response:
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)
CodePudding user response:
alter table tablename
add fieldname datatype check(conditional expression);
Example :
alter table A
add age smalint check(age >= 25);
CodePudding user response:
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;