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?

CodePudding user response：

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.

CodePudding user response：

Once you have created the constraints then there is no difference between constraints that are created:

• inline with the column definition in a CREATE TABLE statement;
• out-of-line with the column definition in a CREATE TABLE statement; or
• in a separate ALTER TABLE statement.

In all the options above you can use either system-generated or user-generated names for the constraints (see the bottom of the answer for examples) and they will all work equivalently.

However, there can be different side-effects arising at the time when when you declare constraints in the CREATE TABLE statement and post-creation with an ALTER TABLE statement.

1. There is a time difference between table creation and constraint creation; this may be an issue if data is inserted before the constraint is created that violates the constraint and may prevent creation of constraints at a later time. If the constraints are created at the same time as the table then it will be impossible to insert data that violates the constraints.
2. If you declare the constraint in the CREATE TABLE statement then you can simplify the statement by omitting the column's data type and Oracle will derive it from the referenced column's data type. You cannot do that if you declare the constraint later.

---

For example:

Given the table:

CREATE TABLE t2 (
  id NUMBER(8,0)
     CONSTRAINT t2__id__pk PRIMARY KEY
);

You could use out-of-line constraints and omit the data type for the foreign key constraint:

CREATE TABLE t1a (
  id    number,
  t2_id,
  CONSTRAINT  t1a__id__pk PRIMARY KEY (id),
  CONSTRAINT  t1a__t2_id__pk FOREIGN KEY (t2_id) REFERENCES t2(id)
);

Or, the same for an inline constraint:

CREATE TABLE t1b (
  id    number
        CONSTRAINT  t1b__id__pk PRIMARY KEY,
  t2_id CONSTRAINT  t1b__t2_id__fk REFERENCES t2(id)
);

Both tables have the same data types for the columns but to create the constraints later you would need to fully declare the data type:

CREATE TABLE t1c (
  id    number,
  t2_id NUMBER(8,0)
);

-- INSERT INTO t1c(id, t2_id) VALUES (1, 1);
-- INSERT INTO t1c(id, t2_id) VALUES (1, -1);

ALTER TABLE t1c ADD CONSTRAINT t1c__id__pk PRIMARY KEY (id);
ALTER TABLE t1c
  ADD CONSTRAINT t1c__t2_id__pk FOREIGN KEY (t2_id) REFERENCES t2(id);

And, in the latter example, you could insert invalid data before creating the constraints that may cause constraint creation to fail.

---

As for your final point, you can explicitly name the constraints or use the system-generated names for constraints with all the above options by just including or omitting the CONSTRAINT <identifier> clause from each constraint definition.

For example:

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

CREATE TABLE t1b_unnamed_constraints (
  id    number
        PRIMARY KEY,
  t2_id REFERENCES t2(id)
);

ALTER TABLE t1c_unnamed_constraints ADD PRIMARY KEY (id);
ALTER TABLE t1c_unnamed_constraints ADD FOREIGN KEY (t2_id) REFERENCES t2(id);

db<>fiddle here