Oracle sql limitation issues
I am having trouble generating sql script oracle. How do you create these two constraints?
- If VID is null then FID must also be null (VID = null -> FID = null)
- There must be exactly one line where VID is null, which also means the FID must be empty due to 1.
Here's what I have so far:
create table Employee(
Id int primary key,
Name varchar(15) not null,
VID int,
FID int
);
source to share
You can get close to what you want without a trigger.
You can use a check constraint for the first one:
alter table Employee add constraint chk_vid_fid
check (vid is not null or fid is null);
You can do the second one using a unique constraint:
create unique index unq_Employee_vid on
Employee(case when vid is null then -1 else id end);
In this formulation, it is assumed that is id
non-negative, since most Ids are usually. If you are using the full range of integer values then I'll be explicit with the line:
create unique index unq_Employee_vid on
Employee(case when vid is null then 'null vid' else cast(id as varchar2(255)) end);
This ensures that there is no more than one vid
null line, not one line. You can't easily get a constraint guaranteeing that exactly one row matters, because empty tables don't match - and tables are empty when they are created.
source to share
This is how you can fulfill your requirement of having exactly one line with VID is NULL
. As I said in my comment on your original question (and Gordon said also in his accepted answer), you cannot do this with just constraints. However, you can do this with a materialized view. Note that you have to create an MV with refresh fast on commit
and it requires certain things for the materialized view log in the base table.
Also: if you try everything exactly as written, the constraint on the MV will fail (of course, because the underlying table will be empty, so there won't be a row where it VID
is NULL
). Add rows to the base table, of which exactly one has a NULL
for VID
, then a commit
transaction, and then run the command alter table
to add a constraint to the MV. From this point the transaction is in the base table (consisting of one or more operators insert
, delete
, update
or merge
followed by one commit
) will pass if and only if it will take exactly one row with NULL
a column VID
.
One oddity to keep in mind is that even if there is a statement in Oracle SQL ALTER MATERIALIZED VIEW
, to add a constraint on MV, we have to use an operator alter table
(named MV), not ALTER MATERIALIZED VIEW
.
Note that I used a name t_Employee
for the base table since I already had a table EMPLOYEE
and I don't want to link to my existing objects.
create table t_Employee(
Id int primary key,
Name varchar(15) not null,
VID int,
FID int
);
alter table t_Employee add constraint chk_vid_fid
check (vid is not null or fid is null)
;
create materialized view log on t_Employee
with rowid
(VID)
including new values
;
create materialized view mv_Employee
refresh fast on commit
as select count(*) ct
from t_Employee
where VID is null
;
alter table mv_Employee add constraint chk_vid_ct
check (ct = 1)
;
source to share