Differences between keys with and w/ ADD CONSTRAINT

March 16, 2022

If i understand correctly, the only difference between

CREATE TABLE t1 (
      id   number,
      t2_id number,
      CONSTRAINT  ExampleTablePK  PRIMARY KEY (id),
      CONSTRAINT  ExampleTableFK  FOREIGN KEY (t2_id) REFERENCES t2(id))

--t1 and t2 already created
ALTER TABLE t1 ADD CONSTRAINT ExampleTablePK  PRIMARY KEY (id);

ALTER TABLE t1 ADD CONSTRAINT  ExampleTableFK  FOREIGN KEY (t2_id) REFERENCES t2(id))

and

CREATE TABLE t1 (
      id   number PRIMARY KEY,
      t2_id number FOREIGN KEY REFERENCES t2(id))

is that the first and second one allow me to name the constraints however I want. The third one autofills the name with sth like sysco00....

But outside from that there are no differences. Right?

>Solution :

There’s also a hybrid between your 1st and 3rd code, that uses an inline constraint clause:

CREATE TABLE t1 (
  id   number    CONSTRAINT  ExampleTablePK  PRIMARY KEY,
  t2_id number   CONSTRAINT  ExampleTableFK  REFERENCES t2 (id))

Whichever approach you take, the final result is just the same – constraints are added to that table.

Apart from the "name" difference, note that the ALTER TABLE option makes more sense especially in a cross-reference situation, where two tables reference each other, e.g.

create table ta (ida number primary key,
                 idb number constraint fkab references b (idb));

create table tb (idb number primary key,
                 ida number constraint fkba references a (ida));

which just can’t work because at the moment of create table ta table tb doesn’t exist yet, so its foreign key constraint can’t be created at all. ALTER TABLE comes into rescue, then.