CODEWITHSUNDEEP
sqloracle-databaseplsqlconstraints
7alhashmi
7alhashmi

Reputation: 924

Can I use select statement with check constraint

My table is parent-child table where it includes two columns. The first column is ID which is auto number. The other column is ParentID which has the special conditions. If the row is parent the ParentID = 0 otherwise it should be related to a ParentID row. For example:

**ID** | **ParentID**
---------------------
1        0
2        0
3        2
4        [it can be 0 or 1 or 2 NOT 3]

The ParentID depends on the ID column, if the ID includes ParentID = 0 then the ParentID can be that ID.

The question is can i do something like this??

constraint ParentID_ck ckeck(ParentID in(0, select ID from table where ParentID = 0;))

Or I should use other solution such as PL/SQL:

DECLARE
--Somthing..
BEGIN
--Somthing..
END;

And put it inside the check constraint.

Upvotes: 3

Views: 3450

Answers (3)

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60272

You can do this by fixing your data model first. Your table has two kinds of entities - Parents and Children. Create a separate table for your Parents, then you can use ordinary referential constraints.

Upvotes: 0

Mohsen Heydari
Mohsen Heydari

Reputation: 7284

You need to implement this constraint with combination of foreign key constraint and a trigger
You may use a null-able foreign key from the table to itself.(parentID == null shows the root nodes).
For the part of select ID from table where ParentID = 0 (null) of the constraint you may use a after insert or update DML trigger, some trigger examples could be found here

Upvotes: 2

user2422457
user2422457

Reputation:

You can do something like: 

insert into TABLE_NAME(PARENT_ID,ID)
(select <THE_PARENT_ID>, <ID> from DUAL where 
  exists (select 1 from TABLE_NAME where PARENT_ID = <THE_PARENT_ID>))

So that those PARENT_ID's which are actually not in the TABLE_NAME would not be inserted.

Upvotes: 0

Related Questions