Date:         Tue, 23 Oct 2001 08:54:56 -0400
Reply-To:     "Fehd, Ronald J." <rjf2@CDC.GOV>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Fehd, Ronald J." <rjf2@CDC.GOV>
Subject:      Re: SQL error with PrimeKey solved
Content-Type: text/plain; charset="iso-8859-1"

<sigh> Celko:
SQL for Smarties, 2e, pg 16-17 UNIQUE and PRIMARY KEY constraints
"... the term PRIMARY KEY in SQL has to do with defaults in referential
actions, ....
A PRIMARY KEY is automatically declared to have a NOT NULL constraint on it,
..."
removing
  1    CHK                  BLANK
 13    CODENONR             Data present
from the data TEST
allows this to run successfully.

-----Original Message-----
From: Fehd, Ronald J.
Sent: Tuesday, October 23, 2001 8:25 AM
To: ___ SAS-L @listserv. uga. edu (E-mail)
Subject: SQL error with PrimeKey

;/*yTestPrimeKeyData
RJF2 01Oct23
Obs    FMTNAME     VALUE    LABEL
;/* . */
data LIBSQL.TEST;
input @8  FmtName $char8.
      @21 Value   $char2.
          @29 Label   $char20.;
*        1         2         3
  1    CHK                  BLANK
 13    CODENONR             Data present
1234567890123456789012345678901234567890
;cards;
  2    CHK          .       BLANK
  3    CHK          X       Yes
  4    HIVREAC      0       NO
  5    HIVREAC      1       YES
  6    LABTYPE      .       BLANK
  7    LABTYPE      0       No Response
  8    LABTYPE      1       Blood Bank
  9    LABTYPE      2       Hospital
 10    LABTYPE      3       Health Dept
 11    LABTYPE      4       Independent
 12    LABTYPE      99      Other
 14    CODENONR     *       MPEP Staff deleted
 15    CODENONR     D       Not interested
 16    CODENONR     G       Other
 17    CODENONR     L       Samples unsuitable
 18    CODENONR     Q       Insufficient sample
 19    CODENONR     R       Reagents unavailable
 20    CODENONR     S       Specimens not tested
 21    CODENONR     T       Test not performed
;
proc PRINT;
run;
%LET DATA_SET = TEST;
%LET PRIMEKEY = FmtName, Value;

proc SQL;
create   table    &DATA_SET.
(
FMTNAME                           char(8),
VALUE                             char(2),
LABEL                             char(20)
,constraint    PrimeKey
 primary key (&PRIMEKEY.)
);
insert into &DATA_SET.
          select       *
          from         LIBSQL.&DATA_SET.
;
describe table    &DATA_SET.;
quit;

- - - LOG
NOTE: Table WORK.TEST created, with 0 rows and 3 columns.
789  insert into &DATA_SET.
790            select       *
791            from         LIBSQL.&DATA_SET.
792  ;
NOTE: 19 rows were inserted into WORK.TEST.

793  describe table    &DATA_SET.;
NOTE: SQL table WORK.TEST was created like:

create table WORK.TEST( bufsize=4096 )
  (
   FMTNAME char(8),
   VALUE char(2),
   LABEL char(20)
  );
create unique index PRIMEKEY on WORK.TEST(FMTNAME,VALUE);

-----Alphabetic List of Integrity Constraints-----
     Integrity
#    Constraint    Type           Variables
1    PRIMEKEY      Primary Key    FMTNAME VALUE
794  quit;
- - - END LOG

Ron Fehd  the knot-yet SQL maven  CDC Atlanta GA USA RJF2@cdc.gov
OpSys: Win_Pro   Ver: 8.2
---> cheerful provider of TESTED SAS code from the KludgeWrx !*! <---

RTFM: I'm an engineer, I don't get paid to know,
I get paid to know where to look it up.
RTFM: Read The Finite Manual.

"What he said!"