IDS Forum

Re: Required to have names for simple constraints?

Posted By: Art Kagel
Date: Thursday, 8 September 2016, at 2:57 p.m.

In Response To: Re: Required to have names for simple constraints? (Fernando Nunes)

Good point Fernando. This is the main reason that I have maintained
myschema for all these years.

The issue happens when a table experiences an ALTER that is not in-place.
That causes the tables tabid to change but the constraints and
auto-generated index names created to support constraints use the table's
original tabid. When tables are imported that can clash with the
constraints for tables created in the new database which happen to have the
other table's original tabid. This is the main reason why myschema always
generates explicit indexes to support constraints and explicit constraint
names unless told not to.

However, note that this is NOT an issue for NOT NULL constraints, CHECK
constraints, or DEFAULT constraints and so myschema does not generate
constraint names for these unless they were explicitly named in the source
database. I would drop the names of NOT NULL constraints and DEFAULTs,
there is no compelling reason to name them. You can make a case for naming
CHECK constaints for the reasons I gave earlier though.

Art

Art S. Kagel, President and Principal Consultant
ASK Database Management
www.askdbmgt.com

Blog: http://informix-myview.blogspot.com/

Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on the IIUG, nor any other organization with which I am
associated either explicitly, implicitly, or by inference. Neither do
those opinions reflect those of other individuals affiliated with any
entity with which I am affiliated nor those of the entities themselves.

On Thu, Sep 8, 2016 at 1:18 PM, Fernando Nunes <domusonline@gmail.com>
wrote:

> Apart of what Art mentioned, automatically generated constraints names may
> give you issues when importing data.
> Depending on how you do it some constraints may not be in the schema and
> when Informix generates them it may conflict with others that were created
> in the original system...
>
> Can't remember the details, but i usually try to provide names at least for
> FK, PK.
>
> I think it's related to the fact that the imported tabid will not be the
> original.... so you may have the same names on different tables that by any
> chance go the original tabid....
>
> Maybe someone can be clearer about this...
>
> On Thu, Sep 8, 2016 at 5:06 PM, MURTARI, JOHN <jm5903@att.com> wrote:
>
> > Folks,
> >
> > Working on IDS 11.5 on Solaris. DB was setup many years ago and has not
> had
> > much mx/tuning since then. Gotten very slow. I'm primarily a MySQL DBA
> and
> > have done a lot of reading on informix. Things are running much better,
> but
> > one remaining step is to truncate some very large tables with a lot of
> > historical data that has never been purged.
> >
> > The summary plan is to create a new table, insert the needed data, drop
> the
> > old, and then do a rename. Restore any indexes, constraints.
> >
> > Got through most of it, but I'm trouble by some diffs I'm seeing in the
> > old/new table schema (two examples below). It seems when the table was
> > created
> > they named every constraint - do I really need to do that for simple
> cases
> > like a "not null" or "primary key" definition. I've read informix
> assigns a
> > generated name. Is there a 'gotcha' I'm mising?
> >
> > < trans_id integer not null constraint "informix".nlbdtrans_id,
> > ---
> > > trans_id integer not null ,
> > AND
> > < primary key (trans_id,recno) constraint "informix".pkbd
> > ---
> > > primary key (trans_id,recno)
> >
> > Many thanks!
> > John
> >
> > ----------------
> > John Murtari - jm5903@att.com<mailto:jm5903@att.com>
> > Ciberspring
> > office: 315-944-0998
> > cell: 315-430-2702
> >
> >
> > ************************************************************
> > *******************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --
> Fernando Nunes
> Portugal
>
> http://informix-technology.blogspot.com
> My email works... but I don't check it frequently...
>
> --94eb2c114d2abe0570053c023902
>
>
> ************************************************************
> *******************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>

--94eb2c0c27f4323946053c0399a6

Messages In This Thread

• Required to have names for simple constraints?
  MURTARI, JOHN -- Thursday, 8 September 2016, at 11:06 a.m.
  • Re: Required to have names for simple constraints?
    Art Kagel -- Thursday, 8 September 2016, at 12:15 p.m.
  • Re: Required to have names for simple constraints?
    Fernando Nunes -- Thursday, 8 September 2016, at 1:18 p.m.
    • Re: Required to have names for simple constraints?
      Art Kagel -- Thursday, 8 September 2016, at 2:57 p.m.
      • Re: Required to have names for simple constraints?
        Fernando Nunes -- Thursday, 8 September 2016, at 3:34 p.m.
      • Re: Required to have names for simple constraints?
        BENJAMIN THOMPSON -- Monday, 12 September 2016, at 7:25 a.m.
  • Re: Required to have names for simple constraints?
    JOHN MURTARI -- Friday, 9 September 2016, at 9:40 a.m.
    • Re: Required to have names for simple constraints?
      Art Kagel -- Friday, 9 September 2016, at 10:23 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.