For a test tomorrow we're told to name our constraints I know it's possible to create a constraint when you use ALTER TABLE but can you add a name to a not null constraint when you CREATE TABLE?
f.e.
CREATE TABLE test (
test1 VARCHAR
CONSTRAINT nn_test1 NOT NULL (test1)
)
I get an error when trying to run this query. Am I writing it wrong?
The error I get is
ERROR: syntax error at or near "NOT" LINE 3: CONSTRAINT nn_test1 NOT NULL (test1)) ^ SQL state: 42601 Character: 56
You have two options to define a named not null constraint:
Inline with the column:
CREATE TABLE test
(
test1 VARCHAR CONSTRAINT nn_test1 NOT NULL,
test2 integer --<< no comma because it's the last column
);
Or at the end of columns as an out-of-line constraint. But then you need a check constraint:
CREATE TABLE test
(
test1 VARCHAR,
test2 integer, --<< comma required after the last column
constraint nn_test1 check (test1 is not null)
);
This has become irrelevant, since you're not using SQL Server
First of all, you should always specify a length for a VARCHAR. Not doing so (in SQL Server variables, or parameters) may result in a string of just exactly ONE character in length - typically NOT what you want.
Then, you need to just specify the NOT NULL - there's no need to repeat the column name (actually this is the error) - if you're specifying the CONSTRAINT "inline" with the column definition (which is a perfectly legal and in my opinion the preferred way of doing this).
Try this code:
CREATE TABLE test
(
test1 VARCHAR(50)
CONSTRAINT nn_test1 NOT NULL
)
At least this is the CREATE TABLE statement that works in T-SQL / SQL Server - not sure about PostgreSQL (don't know it well enough, don't have it at hand to test right now).
I, a_horse_with_no_name, the two syntax:
constraint nn_test1 check (test1 is not null) and test1 VARCHAR CONSTRAINT nn_test1 NOT NULL
are equivalent ? performance correctly ecc. Because in first case the SQL server exception return the name nn_test so the system know exactly error.
