Skip site navigation (1) Skip section navigation (2)

Re: Two Tables That Share Data?

From: Philip Couling <phil(at)pedal(dot)me(dot)uk>
To: Carlos Mennens <carlos(dot)mennens(at)gmail(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Two Tables That Share Data?
Date: 2012-02-08 21:06:08
Message-ID: 4F32E3C0.5070505@pedal.me.uk (view raw or flat)
Thread:
Lists: pgsql-novice
On 08/02/2012 20:34, Carlos Mennens wrote:
> I want to create a separate table in my database called 'dept' and
> basically identify each unique department in my company represented by
> numeric code for example:
>
> Code:
> CREATE TABLE dept
> (
> id SERIAL PRIMARY KEY,
> name VARCHAR(50) UNIQUE NOT NULL,
> email VARCHAR(50) UNIQUE NOT NULL
> );
>
> So this table should look something like:
>
> Code:
>   id |           name           |       email
> ----+--------------------------+--------------------
>    1 | Information Technology   | it(at)myco(dot)tld
>    2 | Configuration Management | cm(at)myco(dot)tld
>    3 | Facility                 | facility(at)myco(dot)tld
>    4 | Software Development     | software(at)myco(dot)tld
>    5 | Finance                  | finance(at)myco(dot)tld
>    6 | Logistics                | logistics(at)myco(dot)tld
>    7 | Inventory                | inventory(at)myco(dot)tld
> (7 rows)
>
> Now I'm going to make a new table called 'employees'&  there is going
> to a field called 'dept' which will have a value from the 'id' field
> in the 'dept' table. My question is how does one traditionally
> configure this in SQL?
>
> When I create my employee table, what data type do I use to create the
> 'dept' field? It will only be storing a low numerical value since I
> only have less than 20 physical dept records. Do I need to create a
> foreign key constraint against this? I'm expecting the data to look
> like this:
>
> Code:
>   id |    name     | dept |      email      |    hire
> ----+-------------+------+-----------------+------------
>    1 | James Smith |    5 | jsmith(at)myco(dot)tld | 2011-04-19
> (1 row)
>
> As you can see the user James Smith is in dept. #5 which is my finance
> dept. Can someone please point me into the right direction on how to
> get this database working as mentioned above?
>
You should keep the data types the same.  SERIAL is in fact INTEGER so 
dept should be INTEGER.

You do not need the foreign key, but it's a good idea.  Foreign keys add 
a little overhead as they
have to be checked when you insert rows.   However foreign keys are a 
good idea.  The provide a
lock against bad data being entered into the database (employees in a 
department that doesn't
exist or a department being deleted while it still has employees).

In response to

Responses

pgsql-novice by date

Next:From: JORGE MALDONADODate: 2012-02-09 15:25:08
Subject: Conditional expresion in query
Previous:From: Carlos MennensDate: 2012-02-08 20:34:38
Subject: Two Tables That Share Data?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group