Establish connection between different tables in a database system

Question

I create different tables in a database system .. now I need to make a relationship between them I mean the primary key in one table should be connected to a foreign key in another table.. Should I do that when I create the table itself or it they will be connected together when I insert the data and they they are identical?

For example I do the following when I create two tables:

CREATE TABLE employee (
  fname    varchar(15),
  minit    varchar(1),
  lname    varchar(15),
  ssn      char(9),
  bdate    date,
  address  varchar(50),
  sex      char,
  salary   decimal(10,2),
  superssn char(9),
  dno      number(4)
);

CREATE TABLE department (
  dname        varchar(25),
  dnumber      number(4),
  mgrssn       char(9), 
  mgrstartdate date
);

Here the dno should be connected to the dnumber while the ssn should be connected to the mgrssn.

Answer 1

Adding the data doesn't do anything special to the constraints on the tables, how is your db going to know that the values are identical? They might look the same but have different meanings in different places.

If your table declaration includes a foreign key reference to your other table then that's all that there is to it.

If not (and I don't see one in your example) then you should add one in order to maintain referential integrity.

You could add constraints after you've added data, but if any data is not valid (like nulls in your foreign key field) then you won't be able to add the constraint.