System Table SYSCONSTRAINTS
The poster wanted to generate "ALTER TABLE ... DROP CONSTRAINT ..." statements for all primary and foreign keys by querying sysconstraints, but was confused by idxname values starting with a blank. Answers: ignore idxname (those are system-generated index names, dropped automatically with the constraint) and use constrname joined to systables on tabid, filtering tabid > 99 and on constrtype (C=check, N=not null, P=primary, R=referential, T=table, U=unique). Sample dbaccess/unload script was given, plus pointers to Art Kagel's myschema utility and a developerWorks article.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
IDS 11.50.FC5 AIX 5.3 (4K Page Size) I am trying to interpret the sysconstraints table in order to write a routine that will, in effect, build an "ALTER TABLE xxx DROP CONSTRAINT yy" line for every primary and foreign key constraint defined within a database. Using the information within this table, is this even possible programmatically? Some of the names within the idxname field being with a leading " " (blank). Thanks in advance. Clifton _________________________________________________________________ Windows 7: Unclutter your desktop. Learn more. http://www.microsoft.com/windows/windows-7/videos-tours.aspx?h=7sec&slideid=1&me dia=aero-shake-7second&listid=1&stop=1&ocid=PID24727::T:WLMTAGL:ON:WL:en-US:WWL_ WIN_7secdemo:122009
Here is an example of how I do it:
dbaccess ${DBNAME} - <<EOT!
unload to '${WORKDIR}/drop_fk.sql'select "alter table " || tabname || " "|| "drop constraint " ||
trim(constrname)
from sysconstraints A, systables B
where A.tabid = B.tabid
and A.constrtype = "R"
and B.tabid > 99
EOT!
That is to get foreign keys only - you can modify at your discretion to get
other types of constraints of course.
MM
Index names that begin with a space are auto created by the constraint and = will be auto dropped when you drop the constraint. Just use the constname = column as the constraint name. Even if you didn't name the constraint, the= enguine have it a name for you. Just make sure you REALLY want to drop = not null constraints or wnt to ignore them. All not null clauses generate = a constraint, even if you used the older syntax without the constraint keyw= ord. Art=20 -----Original Message----- From: Clifton Bean <clifton_bean@hotmail.com> Sent: Thursday, December 03, 2009 12:47 PM To: ids@iiug.org Subject: System Table SYSCONSTRAINTS [18259] IDS 11.50.FC5 AIX 5.3 (4K Page Size)=20 I am trying to interpret the sysconstraints table in order to write a routi= ne=20 that will, in effect, build an "ALTER TABLE xxx DROP CONSTRAINT yy" line fo= r=20 every primary and foreign key constraint defined within a database. Using t= he=20 information within this table, is this even possible programmatically? Some= of=20 the names within the idxname field being with a leading " " (blank).=20 Thanks in advance.=20 Clifton=20 _________________________________________________________________=20 Windows 7: Unclutter your desktop. Learn more.=20 http://www.microsoft.com/windows/windows-7/videos-tours.aspx?h=3D7sec&slide= id=3D1&media=3Daero-shake-7second&listid=3D1&stop=3D1&ocid=3DPID24727::T:WL= MTAGL:ON:WL:en-US:WWL_WIN_7secdemo:122009=20 ***************************************************************************= ****=20 Forum Note: Use "Reply" to post a response in the discussion forum.=20
From Mike's email, foreign key constraints are defined as type "R"; I presume that all primary keys are defined as type "P"? [I wish I could find a data dictionary for the system tables within databases and the same for the sysmaster database.] Take care. Clifton > To: ids@iiug.org > From: art.kagel@gmail.com > Subject: RE: System Table SYSCONSTRAINTS [18263] > Date: Thu, 3 Dec 2009 13:50:58 -0500 > > Index names that begin with a space are auto created by the constraint and = > will be auto dropped when you drop the constraint. Just use the constname = > column as the constraint name. Even if you didn't name the constraint, the= > enguine have it a name for you. Just make sure you REALLY want to drop = > not null constraints or wnt to ignore them. All not null clauses generate = > a constraint, even if you used the older syntax without the constraint keyw= > ord. > > Art=20 > > -----Original Message----- > From: Clifton Bean <clifton_bean@hotmail.com> > Sent: Thursday, December 03, 2009 12:47 PM > To: ids@iiug.org > Subject: System Table SYSCONSTRAINTS [18259] > > IDS 11.50.FC5 AIX 5.3 (4K Page Size)=20 > > I am trying to interpret the sysconstraints table in order to write a routi= > ne=20 > that will, in effect, build an "ALTER TABLE xxx DROP CONSTRAINT yy" line fo= > r=20 > every primary and foreign key constraint defined within a database. Using t= > he=20 > information within this table, is this even possible programmatically? Some= > of=20 > the names within the idxname field being with a leading " " (blank).=20 > > Thanks in advance.=20 > > Clifton=20 > > _________________________________________________________________=20 > Windows 7: Unclutter your desktop. Learn more.=20 > > http://www.microsoft.com/windows/windows-7/videos-tours.aspx?h=3D7sec&slide= > id=3D1&media=3Daero-shake-7second&listid=3D1&stop=3D1&ocid=3DPID24727::T:WL= > MTAGL:ON:WL:en-US:WWL_WIN_7secdemo:122009=20 > > ***************************************************************************= > ****=20 > Forum Note: Use "Reply" to post a response in the discussion forum.=20 > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > _________________________________________________________________ Windows Live Hotmail is faster and more secure than ever. http://www.microsoft.com/windows/windowslive/hotmail_bl1/hotmail_bl1.aspx?ocid=P ID23879::T:WLMTAGL:ON:WL:en-ww:WM_IMHM_1:092009
I've found Art's 'myschema' utility to be great for dumping a schema to a 'table' sql file and 'index/constraint' sql file. Bob ----- Original Message ----- From: "Clifton Bean" <clifton_bean@hotmail.com> To: ids@iiug.org Sent: Thursday, December 3, 2009 2:13:10 PM GMT -05:00 US/Canada Eastern Subject: RE: System Table SYSCONSTRAINTS [18264] >From Mike's email, foreign key constraints are defined as type "R"; I presume that all primary keys are defined as type "P"? [I wish I could find a data dictionary for the system tables within databases and the same for the sysmaster database.] Take care. Clifton > To: ids@iiug.org > From: art.kagel@gmail.com > Subject: RE: System Table SYSCONSTRAINTS [18263] > Date: Thu, 3 Dec 2009 13:50:58 -0500 > > Index names that begin with a space are auto created by the constraint and = > will be auto dropped when you drop the constraint. Just use the constname = > column as the constraint name. Even if you didn't name the constraint, the= > enguine have it a name for you. Just make sure you REALLY want to drop = > not null constraints or wnt to ignore them. All not null clauses generate = > a constraint, even if you used the older syntax without the constraint keyw= > ord. > > Art=20 > > -----Original Message----- > From: Clifton Bean <clifton_bean@hotmail.com> > Sent: Thursday, December 03, 2009 12:47 PM > To: ids@iiug.org > Subject: System Table SYSCONSTRAINTS [18259] > > IDS 11.50.FC5 AIX 5.3 (4K Page Size)=20 > > I am trying to interpret the sysconstraints table in order to write a routi= > ne=20 > that will, in effect, build an "ALTER TABLE xxx DROP CONSTRAINT yy" line fo= > r=20 > every primary and foreign key constraint defined within a database. Using t= > he=20 > information within this table, is this even possible programmatically? Some= > of=20 > the names within the idxname field being with a leading " " (blank).=20 > > Thanks in advance.=20 > > Clifton=20 > > _________________________________________________________________=20 > Windows 7: Unclutter your desktop. Learn more.=20 > > http://www.microsoft.com/windows/windows-7/videos-tours.aspx?h=3D7sec&slide= > id=3D1&media=3Daero-shake-7second&listid=3D1&stop=3D1&ocid=3DPID24727::T:WL= > MTAGL:ON:WL:en-US:WWL_WIN_7secdemo:122009=20 > > ***************************************************************************= > ****=20 > Forum Note: Use "Reply" to post a response in the discussion forum.=20 > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > _________________________________________________________________ Windows Live Hotmail is faster and more secure than ever. http://www.microsoft.com/windows/windowslive/hotmail_bl1/hotmail_bl1.aspx?ocid=P ID23879::T:WLMTAGL:ON:WL:en-ww:WM_IMHM_1:092009 ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
Code identifying the constraint type: C = Check constraint N = Not NULL P = Primary key R = Referential T = Table U = Unique
http://www.ibm.com/developerworks/data/zones/informix/library/techarticle/03 05parker/0305parker.html discusses this j. Sane ego te vocavi. Forsitan capedictum tuum desit. -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org]On Behalf Of Clifton Bean Sent: Thursday, December 03, 2009 12:47 PM To: ids@iiug.org Subject: System Table SYSCONSTRAINTS [18259] IDS 11.50.FC5 AIX 5.3 (4K Page Size) I am trying to interpret the sysconstraints table in order to write a routine that will, in effect, build an "ALTER TABLE xxx DROP CONSTRAINT yy" line for every primary and foreign key constraint defined within a database. Using the information within this table, is this even possible programmatically? Some of the names within the idxname field being with a leading " " (blank). Thanks in advance. Clifton _________________________________________________________________ Windows 7: Unclutter your desktop. Learn more. http://www.microsoft.com/windows/windows-7/videos-tours.aspx?h=7sec&slideid= 1&media=aero-shake-7second&listid=1&stop=1&ocid=PID24727::T:WLMTAGL:ON:WL:en -US:WWL_WIN_7secdemo:122009 **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.