Consider the tables below:
CREATE TABLE T (RECORD_SEQ VARCHAR2(50), COUNTRY VARCHAR2(50), CREATED_BY VARCHAR2(10))
CREATE TABLE ENTITLEMENT (USERID VARCHAR2(10), COUNTRY VARCHAR2(50))
A CREATED_BY user is entitled to create records for a subset of countries based on the information in the entitlement table. Can we enforce a Check Constraint on COUNTRY column in T table only for entitled countries based on the CREATED_BY column value in each row.
I'm unsure exactly what you're trying to achieve; example valid/invalid data helps a lot here!
If the requirement is a user must have a country entry in ENTITLEMENT to insert rows for that country in T, then you can:
- Create a primary/unique key over ENTITLEMENT ( USERID, COUNTRY )
- Have a foreign key on these columns in T back to ENTITLEMENT (assuming CREATED_BY == USERID)
For example:
create table entitlement (
userid varchar2(10), country varchar2(50),
primary key ( userid, country )
);
create table t (
record_seq varchar2(50),
created_by, country,
foreign key ( created_by, country )
references entitlement
);
insert into entitlement values ( 'CHRIS', 'GB' );
insert into entitlement values ( 'SAURABH', 'US' );
insert into t values ( 1, 'CHRIS', 'GB' );
insert into t values ( 2, 'CHRIS', 'US' );
--ORA-02291: integrity constraint (CHRIS.SYS_C009719) violated - parent key not found
insert into t values ( 3, 'SAURABH', 'GB' );
--ORA-02291: integrity constraint (CHRIS.SYS_C009719) violated - parent key not found
insert into t values ( 4, 'SAURABH', 'US' );
select * from t;
RECORD_SEQ COUNTRY CREATED_BY
-------------------------------------------------- ---------- --------------------------------------------------
1 CHRIS GB
4 SAURABH US