Re: foreign keys and row types?

Al Wang wrote: ||Hi, || ||I was wondering, is it possible to create foreign key references ||within a row type definition, or do you have to make the reference ||when you actually create a table? KENDRICKS CHERYL replied: |There are also check constraints and default values. Your foreign key |references can be created when you: | | 1. Create the table i.e.: | Create table some_tbl ( | col1 serial not null, | col2 char(10) CHECK (col3 |0) , | col3 char(1) DEFAULT "1" | PRIMARY KEY (col1) ); | | 2. When you create an index that actual defines the relationship (RI) |between tables along with altering the table to define the constraint: | Create table some_tbl( | col1 serial not null, | col2 char(10) CHECK (col3 |0) , | col3 char(1) DEFAULT "1" ) ; | create unique index pk_some_tbl on some_tbl(col1); | | Alter table some_tbl add constraint primary key (col1) constraint |some_tbl_pk; | |I prefer the later (#2) so that indexes can be named with some meaning |verses the other (#1) since this would create an Informix default NAMED |index. Also you can drop the constraint and still keep the consistency |in your data, so that you can later apply it if needed. This of course |is just two reason's of using the later, I know other DBAs have maybe |more. --- SNIP --- Cheryl, actually, you *can* name a constraint when imposed while creating the table. Take the first form you cited: Create table some_tbl ( col1 serial not null, col2 char(10) CHECK (col3 |0) , col3 char(1) DEFAULT "1" constraint def_some_tbl_col3 PRIMARY KEY (col1) CONSTRAINT pk_some_tbl ); There, I've named both constraints in-line. Note that Cheryl's example was for a primary key. The same syntax applies to a foreign key constraint: create table other_tbl( cola serial not null constraint cola_nn, colb integer foreign key references some_tbl constraint other_fk colc char(10) -- Hey, it oughta have SOME data! ); Note now that I have specified the constraint within the column definition. This is just to show the [bit of] flexibility in the syntax; the preferred way is still to specify referential constraints after the column definitions (as demostrated by Cheryl's example). There is another sound reason for using the ALTER TABLE to create constraints. This is when you want to create a primary key constraint on a table but have the index that supports that constraint be in a different dbspace. With form (1) this is impossible - the index that backs the constraint is created in the same dbspace as the table. The famous workaround is to: - Create the table with no PK constraint. (NOT NULL is OK here.) - Create a unique index based on the columns you wanted to create the PK constraint on. Locate this index in a DBspace other than the one in which you created the table. - ALTER TABLE to create the PK constraint as Cheryl shows above. The engine will check if there is already a unique index on the specified columns, not caring which dbspace this index is in. It will then use that index as the support structure for the primary key constraint. -- -- Jake (Never yelled "CROWDED THEATER!" during a fire) +------------------------------------------------------------+ | The expedient performance of a task with excessive concern | | regarding its duration-to-completion engenders a virtual | | certainty of diminished benefit therefrom. | | -- Benjamin Franklin (but he said it in 3 words) | +------------------------------------------------------------+