Home » SQL & PL/SQL » SQL & PL/SQL » check constraint in the same table (merged 3)
check constraint in the same table (merged 3) [message #344256] Fri, 29 August 2008 04:27 Go to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
hi

I have one table as follows
Table Name gl_mst
gl_code  char(4)
gl_desc  char(4)
grp_code char(3) 


I have following test data
gl_code   gl_desc               grp_code
0000      dummy-glcode          null
000       dummy-group-code      null
BS        Balance sheet         null
2401      Cash Account          BS


I want to create a constraint/ trigger . Whenever i insert a record in gl_mst, grp_code can be either null or must exist in gl_code of the same table.

how to create a contraint?

In my earlier attemp i have created another group mst and grp_code was given as foriegn key. Now I like to check the existing table itself


yours
dr.s.raghunathan
check constraint in the same table (merged) [message #344257 is a reply to message #344256] Fri, 29 August 2008 04:29 Go to previous messageGo to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
hi

I have one table as follows
Table Name gl_mst
gl_code  char(4)
gl_desc  char(4)
grp_code char(3) 


I have following test data
gl_code   gl_desc               grp_code
0000      dummy-glcode          null
000       dummy-group-code      null
BS        Balance sheet         null
2401      Cash Account          BS


I want to create a constraint/ trigger . Whenever i insert a record in gl_mst, grp_code can be either null or must exist in gl_code of the same table.

how to create a contraint?

In my earlier attemp i have created another group mst and grp_code was given as foriegn key. Now I like to check the existing table itself


yours
dr.s.raghunathan
creation of check constraint in the same table [message #344260 is a reply to message #344257] Fri, 29 August 2008 04:31 Go to previous messageGo to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
hi

I have one table as follows
Table Name gl_mst
gl_code  char(4)
gl_desc  char(4)
grp_code char(3) 


I have following test data
gl_code   gl_desc               grp_code
0000      dummy-glcode          null
000       dummy-group-code      null
BS        Balance sheet         null
2401      Cash Account          BS


I want to create a constraint/ trigger . Whenever i insert a record in gl_mst, grp_code can be either null or must exist in gl_code of the same table.

how to create a contraint?

In my earlier attemp i have created another group mst and grp_code was given as foriegn key. Now I like to check the existing table itself


yours
dr.s.raghunathan
Re: creation of check constraint in the same table [message #344262 is a reply to message #344260] Fri, 29 August 2008 04:35 Go to previous messageGo to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
sorry

wrongly repeated thrice as fresh topic. Mistake has been happened due to suspect on connectivity and technical fault on my PC

yours
dr.s.raghunathan
Re: creation of check constraint in the same table [message #344266 is a reply to message #344260] Fri, 29 August 2008 04:37 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Create a Foreign Key constraint from gl_mast(grp_code) to gl_mast(gl_code)
Re: creation of check constraint in the same table [message #344267 is a reply to message #344266] Fri, 29 August 2008 04:44 Go to previous messageGo to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
dear Jrowbottom,

two doubts are arised.

1. In some of the rows I need grp_code as null.
I am not able to create gl_code as null.

2. Length of the column varies as gl_code char(4) and grp_code char(3) of course required 3 char only fed in gl_code.

yours
dr.s.raghunathan

Re: creation of check constraint in the same table [message #344269 is a reply to message #344267] Fri, 29 August 2008 04:53 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
1) A FK constraint does not have to be populated. The constraint simply requires that if it is populated then it must refer to a value specified by the constraint.


2) I'm guessing that you didn't bother to try this for yourself:
SQL> create table test_0071 (col_1  char(4), r_col_1 char(3));

Table created.

SQL> alter table test_0071 add constraint test_0071_pk primary key(col_1);

Table altered.

SQL> alter table test_0071 add constraint test_0071_fk foreign key (r_col_1) references test_0071(col_1);

Table altered.
Re: problem in check constraint refering the same table values [message #344274 is a reply to message #344257] Fri, 29 August 2008 05:05 Go to previous messageGo to next message
ramanajv1968
Messages: 168
Registered: December 2005
Location: HYDERABAD
Senior Member
By Using self referencial constraint we can achieve this
Below is the code:

CREATE TABLE GL_MST(GL_CODE  CHAR(4) CONSTRAINT GL_CODE_UK UNIQUE  ,GL_DESC  CHAR(4),GRP_CODE CHAR(3) REFERENCES GL_MST(GL_CODE ))


insert into gl_mst values(null,'du',NULL);
insert into gl_mst values(1,'du',null);
insert into gl_mst values(2,'du',3);
insert into gl_mst values(2,'du',3)
*
ERROR at line 1:
ORA-02291: integrity constraint (PROVENCE.SYS_C008832) violated - parent key
not found
Re: problem in check constraint refering the same table values [message #344276 is a reply to message #344257] Fri, 29 August 2008 05:09 Go to previous messageGo to next message
gaganzk
Messages: 36
Registered: May 2008
Member
Hi Buddy,

Please Try This Approach.....and let us now

CREATE TABLE TESTGL
(
GL_CODE VARCHAR2(4 BYTE),
GL_DESC VARCHAR2(4 BYTE),
GRP_CODE VARCHAR2(3 BYTE) DEFAULT NULL
)

ALTER TABLE TESTGL ADD (
CONSTRAINT TEST_PK
PRIMARY KEY
(GL_CODE)
USING INDEX
TABLESPACE USERS
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
));

ALTER TABLE TESTGL ADD (
CONSTRAINT TESTREF
FOREIGN KEY (GRP_CODE)
REFERENCES TESTGL (GL_CODE));


Regards:
Gagan Deep Kaushal
Re: creation of check constraint in the same table [message #344280 is a reply to message #344269] Fri, 29 August 2008 05:29 Go to previous messageGo to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
Quote:

I'm guessing that you didn't bother to try this for yourself:



Actually i tried but the following error had come while creating the fk

The TABLE operation was not successful for the following reason: 
ORA-02270: no matching unique or primary key for this column-list




Then based on your reply i found that i have made a mistake on object browser ticked the not null box while creating the gl_mst.
Thank you very much for the solution offered

yours
dr.s.raghunathan
Re: creation of check constraint in the same table [message #344342 is a reply to message #344260] Fri, 29 August 2008 07:57 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
dr.s.raghunathan wrote on Fri, 29 August 2008 05:31

I have one table as follows
Table Name gl_mst
gl_code  char(4)
gl_desc  char(4)
grp_code char(3) 


I have following test data
gl_code   gl_desc               grp_code
0000      dummy-glcode          null
000       dummy-group-code      null
BS        Balance sheet         null
2401      Cash Account          BS




You are using a CHAR datetype with only 2 or 3 characters. You do know the consequences, right?
Re: creation of check constraint in the same table [message #344364 is a reply to message #344342] Fri, 29 August 2008 09:33 Go to previous messageGo to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
Quote:


You are using a CHAR datetype with only 2 or 3 characters. You do know the consequences, right?




My 240 Gl_codes are getting populated with 4 characters and 11 codes are less than 4 characters. I do not know the consequences. do i need to declare the datatype with varchar2 or is there any other drawbacks available . I may be advised suitably
yours
dr.s.raghunathan
Re: creation of check constraint in the same table [message #344397 is a reply to message #344364] Fri, 29 August 2008 12:34 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
If you try to query with
where gl_code='000';

you will get no rows because CHAR datatype pads with spaces.
Re: creation of check constraint in the same table [message #344398 is a reply to message #344364] Fri, 29 August 2008 12:35 Go to previous message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
hi, check if this helps.
Previous Topic: window function error
Next Topic: Update Year portion in date column
Goto Forum:
  


Current Time: Sat Dec 10 08:47:48 CST 2016

Total time taken to generate the page: 0.24609 seconds