SQL provides two syntactic forms for defining constraints: column-level (or “inline”) and table-level (or “out-of-line”). The column-level syntax bundles the constraint as part of the column definition, but has the limitation that it can only refer to that one column. The NOT NULL constraints you used last time were unnamed, column-level constraints. Here is a column-level primary key constraint:

Scientist_Num varchar2(6) primary key,

The table-level syntax puts the constraint definition at the end of the CREATE TABLE statement, at the same level of nesting as the other columns. Table constraints can apply to multiple columns (within the same table).

We recommend that you define all primary and foreign keys as table-level constraints, for clarity and consistency.

Some SQL dialects also support schema-level constraints, which can involve columns from multiple tables.

Constraint Names

Every constraint has a name, which must be unique within the schema. Constraint names are helpful to developers and administrators, and also to end-users, who may see them in error messages. The system does not try to interpret constraint names, however.

If you do not specify a name for a constraint, the system will generate one automatically. Such names will probably not be very meaningful (e.g. “SYS_C0023877”), so this is not considered good practice. Instead, use the CONSTRAINT keyword, followed by a concise but descriptive name, and then the constraint definition, like so:

constraint Scientist_PK primary key (Scientist_Num)

A useful convention is to begin the constraint name with the table name, so that the table can be easily identified, and constraints for the same table will sort together. Include the column name(s) if appropriate (usually not for primary and foreign keys), and a brief indication of the purpose of the constraint (e.g. PK for primary key, FK for foreign key, or Range, Valid, or similar for other types).

If a constraint name is too long, you will see an error. Sensibly abbreviate the name and try again.

The system only cares about the constraint definition (the last part), and does not try to interpret the name.

You may choose not to name your NOT NULL constraints, however, as they are very common and error reports a relatively helpful and specific error message for these.

Testing Constraints

Just because your code runs without errors does not mean it is correct! When developing any database integrity constraint, you should design some test cases and make sure that they are treated correctly. You should check that invalid data are rejected, but also that valid data are not unintentionally rejected. Test especially around “edge cases”, such as values near the boundary between valid and invalid. For primary key constraints, you should test both the unique and not null properties.

Normally your test cases will be INSERT statements, though you can test UPDATE and DELETE operations as well. When an operation fails, check the error message carefully to make sure that it failed in the way you expected, and not for some other reason (e.g. missing punctuation).