28 May

SAS: Constraints

This will be ugly.  I’m looking at the Study Guide and it does a terrible job on constraints.  For instance, it mentions referential integrity (foreign keys) but it does not show an example.  However, it also lists several keywords that would apply to it.  So, do I need to know it for the test?

There are about 6 pages on the guide so I’m going to try and dig out what I think I would need to know.  Since I already know most of this, from other databases, I’ll surely skip useful parts.  And, no way am I doing each database version. This post is only about SAS.

Anyways, if it helps, great,  If it doesn’t, well, you got what you paid for, I guess.

CHECK

A check constraint is similar to a WHERE clause but it’s applied at the column level.  For example,

proc sql;
CREATE TABLE work.myjunk
(
	name char(10) CHECK (name IN ('Billy','Bob','Thornton')),
	number int CHECK (number < 10)
);
DESCRIBE TABLE CONSTRAINTS work.myjunk;
quit;     Integrity              Where
#    Constraint    Type     Clause
----------------------------------------------------------------
1    _CK0001_      Check    name in ('Billy', 'Bob', 'Thornton')
2    _CK0002_      Check    number

This syntax creates two CHECK constraints:

  • name: It must be one of Billy, Bob or Thornton.
  • number: It must be less than 10.

Because we didn’t name the constraints, SAS assigned a default name to them. I’m not going to list them as they’ll be in the sample code for each constraint. You can also name a constraint through the ALTER TABLE statement or adding the constraint to the end of the CREATE TABLE syntax as follows:

proc sql;
CREATE TABLE work.myjunk
(
	name char(10),
	number int,
	CONSTRAINT mycheck CHECK (number < 10)
);

DESCRIBE TABLE CONSTRAINTS work.myjunk;
quit;
     Integrity               Where
#    Constraint     Type     Clause
-----------------------------------------------------------------
1    mycheck        Check    number

I won’t do this every time. This is already going to be a very long, and it’s already disorganized enough, but I’ll try to sprinkle the variations throughout the post so you are comfortable seeing them both ways.

A CHECK constraint can also apply to a different column in the same row.

proc sql;
CREATE TABLE work.myjunk
(
	number int,
	number1 int CHECK (number1 < number)
);
DESCRIBE TABLE CONSTRAINTS work.myjunk;
quit;

     Integrity              Where
#    Constraint    Type     Clause
------------------------------------------
1    _CK0001_      Check    number1<number

This is precisely the type of thing I would expect SAS to ask you on a test.

NOT NULL

NOT NULL simply says no NULL, or missing (this is SAS) values, in the column. You write the constraint as follows:

proc sql;
CREATE TABLE work.myjunk
(
	name char(10) NOT NULL,
	number int,
	CONSTRAINT myconstraint NOT NULL (number)
);
DESCRIBE TABLE CONSTRAINTS work.myjunk;
quit;

     Integrity
#    Constraint      Type        Variables
------------------------------------------
1    _NM0001_        Not Null    name     
2    myconstraint    Not Null    number

I thought you could add this with an ALTER TABLE / MODIFY statement but apparently not.

proc sql;
CREATE TABLE work.myjunk
(
	name char(10) NOT NULL,
	number int
);
ALTER TABLE work.myjunk MODIFY number int NOT NULL;
DESCRIBE TABLE CONSTRAINTS work.myjunk;
quit;

     Integrity
#    Constraint    Type        Variables
----------------------------------------
1    _NM0001_      Not Null    name

To be honest, I was sure it, would work. In MySQL:

mysql> CREATE TABLE work.myjunk
    -> (
    ->  name char(10) NOT NULL,
    ->  number int
    -> );
Query OK, 0 rows affected (0.11 sec)

mysql> ALTER TABLE work.myjunk MODIFY number int NOT NULL;
Query OK, 0 rows affected (0.28 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc work.myjunk;
+--------+----------+------+-----+---------+-------+
| Field  | Type     | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| name   | char(10) | NO   |     | NULL    |       |
| number | int(11)  | NO   |     | NULL    |       |
+--------+----------+------+-----+---------+-------+

The Null column shows NO for Nulls so it worked just fine on that side of the house.  I guess the answer is to only define a NOT NULL constraint in the CREATE TABLE statement for SAS.  You should probably also make a mental note of _NM0001_.  The automatic naming policy of SAS uses NM, not NN, for NOT NULL.  Yeah, that’s logical.  I suppose that’s how they keep the trick questions flowing.

UNIQUE

This constraint simply specifies that all values in the column must be unique. The study guide doesn’t mention this but UNIQUE usually allows for a NULL / missing value, but, only one as two would violate the constraint.

proc sql;
CREATE TABLE work.myjunk
(
	name char(10) UNIQUE,
	number int
);
DESCRIBE TABLE CONSTRAINTS work.myjunk;
quit;

     Integrity
#    Constraint    Type      Variables
--------------------------------------
1    _UN0001_      Unique    name

The constraint seems to work similarly to the NOT NULL constraint in terms of how you define it, however, you may want to apply it to multiple columns as follows:

proc sql;
CREATE TABLE work.myjunk
(
	name char(10),
	number int,
	CONSTRAINT myconstraint unique (number, name)
);
DESCRIBE TABLE CONSTRAINTS work.myjunk;
quit;
     Integrity
#    Constraint      Type      Variables
------------------------------------------
1    myconstraint    Unique    number name

PRIMARY KEY

SAS defines the PRIMARY KEY as a combination of the UNIQUE and NOT NULL constraints. I’m assuming, and have not verified this, yet, that unlike SQL Server, or INNODB tables in MySQL that it is not clustered. However, I haven’t hit the index part of the Study Guide so SAS may do something like that.

There can only be one primary key on a table. ALL of the following can be used to create a primary key.

proc sql;
CREATE TABLE work.myjunk
(
	name char(10),
	number int PRIMARY KEY
);
DESCRIBE TABLE CONSTRAINTS work.myjunk;
quit;

     Integrity
#    Constraint    Type           Variables
-------------------------------------------
1    _PK0001_      Primary Key    number

proc sql;
CREATE TABLE work.myjunk
(
name char(10),
number int,
CONSTRAINT mypk PRIMARY KEY (number)
);
DESCRIBE TABLE CONSTRAINTS work.myjunk;
quit;

Integrity
# Constraint Type Variables
-------------------------------------------
1 mypk Primary Key number

proc sql;
CREATE TABLE work.myjunk
(
name char(10),
number int,
CONSTRAINT mypk PRIMARY KEY (name,number)
);
DESCRIBE TABLE CONSTRAINTS work.myjunk;
quit;

     Integrity
#    Constraint    Type           Variables
---------------------------------------------
1    mypk          Primary Key    name number

The last version specifies that both the name and number fields are part of the primary key and that the restrictions for a primary key apply to both columns. For instance, the combination of the two columns must be unique, but you can repeat a value in a column. For example, this is ok,

name  number
A     1
A     2

But this would not be,

name  number
A     1
A     1

This also applies to a unique constraint that isn’t a primary key.

I’m going to stop now. I find myself getting a bit mixed up on the syntax with all the copy/pasting I’m doing and I fear I’m going to blow it. I think, however, this should give a decent starting point to constraints even if it feels woefully incomplete to me.