index create and ic create statements

[May15]

Hi all,

I have a doubt regarding the creation of indexes and integrity constraints. Please consider the code below.

proc datasets library=ex_library; 
   modify tableA; 
   index create column1;
quit; 
	  
proc datasets library=ex_library;
   modify tableA;
   ic create test_pk = primary key (column1 column2);
quit;

Is it necessary to have the statement to create the index column1 if we create the primary key (ic statement) that contains this column?

And what about the following example?

proc datasets library=ex_library; 
   modify tableB; 
   index create column1;
quit; 
	  
proc datasets library=ex_library;
   modify tableB;
   ic create foreign key column1 references ex_library.tableC on update Restrict on delete Restrict;
 quit;

Do we need the index create statement of a column that is a foreign key?

Thanks,

May15

[kiranv_]

Answer for first question is NO and second one is NO too. Below information from SAS documentation goes in detail about this.

Indexes and Integrity Constraints

The unique, primary key, and foreign key integrity constraints store data values in an index file. If an index file already exists, it is used; otherwise, one is created. Consider the following points when you create or delete an integrity constraint:

• When a user-defined index exists, the index's attributes must be compatible with the integrity constraint in order for the integrity constraint to be created. For example, when you add a primary key integrity constraint, the existing index must have the UNIQUE attribute. When you add a foreign key integrity constraint, the index must not have the UNIQUE attribute.

• The unique integrity constraint has the same effect as the UNIQUE index attribute; therefore, when one is used, the other is not necessary.

• The NOMISS index attribute and the not-null integrity constraint have different effects. The integrity constraint prevents missing values from being written to the SAS data file and cannot be added to an existing data file that contains missing values. The index attribute allows missing data values in the data file but excludes them from the index.

• When any index is created, it is marked as being "owned" by the user, the integrity constraint, or both. A user cannot delete an index that is also owned by an integrity constraint and vice versa. If an index is owned by both, the index is deleted only after both the integrity constraint and the user have requested the index's deletion. A note in the log indicates when an index cannot be deleted.