BookmarkSubscribeRSS Feed
May15
Obsidian | Level 7

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

1 REPLY 1
kiranv_
Rhodochrosite | Level 12

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 1 reply
  • 975 views
  • 2 likes
  • 2 in conversation