Date:         Mon, 15 Mar 2004 05:33:24 -0800
Reply-To:     Xavier Autret <xav_x@NOOS.FR>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Xavier Autret <xav_x@NOOS.FR>
Organization: http://groups.google.com
Subject:      Re: Constraints Information
Content-Type: text/plain; charset=ISO-8859-1

Your "NOT NULL" constraint does not exist.

use that code:
<sasl:code>
proc sql;
   alter table test1 add constraint notNull not null (a3);
quit;

*-- Or this one --*;
proc sql;
   create table test1
   (a1 NUM
   ,a2 NUM
   ,a3 CHAR(8)
   ,constraint onA3 CHECK(a3 ^= 'Z') message='a3 must be ^= "Z"' msgtype=user
   ,constraint onA1 not null (a1) message='a1 can not be null' msgtype=user
   )
   ;
quit;

</sasl:code>

Xavier
> Thanks Autret,
>
> Now I can get information on CHECK constraints but I am still not
> getting information on NOT NULL constraints.
>
> Regards,
> Nitin Chandak
> Stingray Technologies Pvt. Ltd.
> B-10, Sector 1, Udyog Marg,
> Noida-201 301 (NCR Delhi).
> nitin.chandak@globalcase.net
> Mobile : 09811431769
>
>
>
> -----Original Message-----
> From: SAS(r) Discussion [mailto:SAS-L@VM.MARIST.EDU]On Behalf Of Xavier
> Autret
> Sent: Friday, March 12, 2004 5:55 PM
> To: SAS-L@VM.MARIST.EDU
> Subject: Re: Constraints Information
>
>
> 2 Solutions:
>
> Use out2 in proc contents
> Use ods and the IntegrityConstraints table.
>
> <sasl:code>
> proc contents data=test1 out=test2 out2=test3 noprint;
> run;
>
> ods output IntegrityConstraints=test4;
> ods listing close;
> proc contents data=test1;
> run;
> ods output close;
> ods listing;
> </sasl:code>
>
> ps: why use meta tags such as <s a s l : c o d e> or <s a s>?
>
> Xavier
>
> nitin.chandak@GLOBALCASE.NET (Nitin Chandak) wrote in message
> news:<OMEPIBFDDCKOCCEEDAJLAEOGCBAA.nitin.chandak@globalcase.net>...
> > Hi All,
> >
> > Run the below code. You will find out dataset test2 is having all the
> > information
> > related to dataset test1 except information on constraints.
> >
> > How to find out constraints and not null columns information from
>  dictionary
> > tables
> > or from any PROC?
> >
> > Thanks in advance.
> >
> > <SAS>
> > data test1;
> > infile datalines;
> > input a1 a2 a3 $;
> > datalines;
> > 1.00 2.00 a
> > 4.00 5.00 b
> > 7.00 8.00 c
> > run;
> >
> > proc sql;
> > alter table test1 add constraint ck check (a3 NE 'Z');
> > alter table test1 modify a3 not null;
> > describe table constraints test1;
> > insert into test1 values (1,10,'a');
> > insert into test1 values (1,20,'Z');
> > quit;
> >
> > proc contents data=test1 out=test2 noprint;
> > run;
> >
> > proc print data=test2; run;
> >
> > </SAS>
> >
> >
> > Regards,
> > Nitin Chandak
> > Stingray Technologies Pvt. Ltd.
> > B-10, Sector 1, Udyog Marg,
> > Noida-201 301 (NCR Delhi).
> > nitin.chandak@globalcase.net
> > Mobile : 09811431769