DATA Step, Macro, Functions and more

SQL integrity constraints, a strange result...

Reply
Contributor
Posts: 39

SQL integrity constraints, a strange result...

Hi to all,

I had a little problem today with a SQL program I wrote.
I tried to simplify this problem as much as possible - my comments are included into the program - it should work if you copy/paste into your SAS editor.

so let's start :

PROC SQL ;
CREATE TABLE SES1M1
(prenom CHAR(15));
** first round ;
ALTER TABLE SES1M1
ADD PRIMARY KEY(prenom);
DESCRIBE TABLE ses1m1;
/* if you take a look at your log here, you'll see that this IC is presented as an unique index -
create table WORK.SES1M1( bufsize=4096 )
(
prenom char(15)
);
create unique index prenom on WORK.SES1M1(prenom);

no problem here */;

** second round ;
ALTER TABLE SES1M1
DROP CONSTRAINT _PK0001_ ;
DESCRIBE TABLE ses1m1;
/* I'm deleting the IC - the describe table statement is indicating :
create table WORK.SES1M1( bufsize=4096 )
(
prenom char(15)
);
the unique index is gone - still no problem */;

** third round ;
ALTER TABLE SES1M1
ADD distinct(prenom);
DESCRIBE TABLE ses1m1;
** I'm creating another IC here, still presented as an unique index - no problem here ;

** fourth round : let's create a problem ;
ALTER TABLE SES1M1
DROP CONSTRAINT _PK0001_ ;
DESCRIBE TABLE ses1m1;
** the _PK0001_ constraint is not existing - see the LOG - the unique index is not deleted ;

** fifth round : beginning of fun ;
ALTER TABLE SES1M1
ADD PRIMARY KEY (prenom);
** the UNIQUE IC is transformed into a PK ;
ALTER TABLE SES1M1
DROP CONSTRAINT _PK0001_ ;
** let's delete this PK ;
DESCRIBE TABLE ses1m1;
** and go now into the log to see that the unique index IS NOT DELETED even if the PK is deleted...;

** sixth round : the consequences ;
ALTER TABLE SES1M1
ADD PRIMARY KEY (prenom);
DESCRIBE TABLE ses1m1;
* and see your log : impossible to create a new IC since the unique index is still valid ;

The solution is not a problem : let's delete this index.

now my question :
is there a bug here or is there something special I don't understand ?

The fact that after the error (fourth round), the unique index is not deleted when the PK IC is deleted is puzzling me.

Why this error is having consequences on the functioning of SQL/SAS after the error has occured ?

best regards (from France)

Sébastien
Ask a Question
Discussion stats
  • 0 replies
  • 418 views
  • 0 likes
  • 1 in conversation