Why DB constraints are not added during table creation.

Question

Hi All,

What is the difference between these to ways of table creation.

CREATE TABLE TABLENAME(
field1....
field2...

add constraint constraint1;
add constraint constraint2;
)

AND

CREATE TABLE TABLENAME(
    field1....
    field2...
    )

ALTER TABLE TABLENAME add constaint1
ALTER TABLE TABLENAME add constaint2

Moreover the first scripts fails on the SQL+ but they pass on sqldeveloper Thanks! Pratik

Answer 1

Hi Pratik,

The difference seems to be that the first method is a single statement whereas the second one uses three distinct statements. IF the statements succeed, the overall result will be the same.

You might want to check your synthax though (especially the use of ";"):

SQL> CREATE TABLE table1 (
  2     field1 NUMBER,
  3     field2 NUMBER,
  4     CONSTRAINT pk_table1 PRIMARY KEY (field1),
  5     CONSTRAINT chk_table1 CHECK (field2 > 0)
  6  );     
Table created

SQL> CREATE TABLE table2 (
  2     field1 NUMBER,
  3     field2 NUMBER);     
Table created

SQL> ALTER TABLE table2 ADD CONSTRAINT pk_table2 PRIMARY KEY (field1);     
Table altered

SQL> ALTER TABLE table2 ADD CONSTRAINT chk_table2 CHECK (field2 > 0);     
Table altered