Sql-server – How to create 3 tables with references On SQL Server

sql serversql-server-2012

I started using SQL Server recently and I think this is simple to resolve.

I need to create one table with a country list, and two tables that will use the country list in a column. Here is the code:

1 – (uf to log a country list)

CREATE TABLE uf
(
id int primary key,
uf varchar(2), (dont know if can I use a column with the same name of the table)
);

2 – the resumed table that will use the list.

CREATE TABLE carta
(
id int primary key,
uf varchar(2) not null,
);

3 – the resumed table that will use the list.

CREATE TABLE cliente
(
id int primary key,
uf varchar(2) not null,
);

I'm really thankful if someone helps me

If have some errors in the writing, I'm sorry, I'm from Brazil and don't know English very well

Best Answer

I've re-written your CREATE TABLE statements like this:

CREATE TABLE dbo.uf
(
    uf_id INT NOT NULL
        CONSTRAINT PK_uf
        PRIMARY KEY CLUSTERED
    , uf_name VARCHAR(50) -- you *can* name a column the same as the table
                         -- but try not to, since it will result in confusion
);

CREATE TABLE dbo.carta
(
    carta_id INT NOT NULL
        CONSTRAINT PK_carta
        PRIMARY KEY CLUSTERED
    , uf_id INT NOT NULL
        CONSTRAINT FK_carta_uf
        FOREIGN KEY REFERENCES
        dbo.uf(uf_id)
    , carta_name VARCHAR(50) NOT NULL
);

CREATE TABLE dbo.cliente
(
    cliente_id INT NOT NULL
        CONSTRAINT PK_cliente
        PRIMARY KEY CLUSTERED
    , uf_id INT NOT NULL
        CONSTRAINT FK_cliente_uf
        FOREIGN KEY REFERENCES
        dbo.uf(uf_id)
    , cliente_name VARCHAR(50) NOT NULL
);

Always specify the "schema" of the table; in the above example that is dbo - which is the default schema. Specifying the schema name removes confusion about what schema will contain the table. I've renamed the uf column in the uf table to uf_name to remove confusion when the uf table is used in queries. You can name a column the same name as the table it appears in, but I would try not to do that.

The dbo.uf table has a PRIMARY KEY constraint which enforces uniqueness and makes the contents of the referenced column valid for use as a foreign key in other, related tables.

The dbo.carta and dbo.cliente tables have FOREIGN KEY references to the dbo.uf table. This prevents inserting rows into those tables without a valid entry in the dbo.uf table. This is commonly referred to as referential integrity and is an important principle in good database design.

I've made all the columns NOT NULL since non-null values are easier to understand, especially when learning.

Below, we're inserting data into the three tables in a way that shows how the foreign keys work. First, we'll insert two country names into the dbo.uf table:

INSERT INTO dbo.uf(uf_id, uf_name)
VALUES (1, 'Brazil')
    , (2, 'Canada');

SELECT *
FROM dbo.uf;

The results:

enter image description here

The uf_id column is used in the other two tables, instead of the actual name of the country itself:

INSERT INTO dbo.carta (carta_id, uf_id, carta_name)
VALUES (1, 1, 'Natal') --Brazil
    , (2, 1, 'Fortaleza') --Brazil, again
    , (3, 2, 'Winnipeg'); --Canada

SELECT *
FROM dbo.carta;

enter image description here

INSERT INTO dbo.cliente (cliente_id, uf_id, cliente_name)
VALUES (1, 1, 'Paulo') --Brazil
    , (2, 2, 'Nedja') --Canada
    , (3, 2, 'Max'); --Canada, again

SELECT *
FROM dbo.cliente;

enter image description here

To join the three tables together, you use the JOIN clause, such as:

SELECT *
FROM dbo.uf
    LEFT JOIN dbo.carta ON uf.uf_id = carta.uf_id
    LEFT JOIN dbo.cliente ON uf.uf_id = cliente.uf_id

which shows the following results:

enter image description here

Steve Stedman made a fantastic poster about the different kinds of JOIN statements, which is available here.

Related Question