2

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

  • 1
    Why would you ever have a varchar(2)? How often will this contain 0 or 1 character? Should probably be char(2) unless you have an unchangeable app that will barf on trailing spaces. Also, please always specify the schema prefix. – Aaron Bertrand Apr 13 '16 at 17:56
3

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.

2

It sounds like what you want is a FOREIGN KEY. This enforces referential integrity between two tables. In other words in your case cliente.uf can never have a value in it that doesn't exist in uf.uf (yes a column can be named the same as a table). Here is the code for your tables:

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

-- Column(s) must have a unique index to be referenced by a foreign key
CREATE UNIQUE INDEX ix_uf ON uf(uf);

CREATE TABLE carta
(
id int primary key,
uf varchar(2) not null CONSTRAINT fk_carta_uf FOREIGN KEY (uf) REFERENCES uf(uf)
);

CREATE TABLE cliente
(
id int primary key,
uf varchar(2) not null CONSTRAINT fk_cliente_uf FOREIGN KEY (uf) REFERENCES uf(uf)
);

Note that there MUST be a UNIQUE INDEX on the column(s) referenced by a foreign key.

  • I'm really thankfull, thank your time to answer Kenneth – Flávio Augusto Apr 13 '16 at 17:32
  • 1
    There must be a UNIQUE index or a UNIQUE constraint. (or a PRIMARY KEY constraint, if we want to be really pedantic) – ypercubeᵀᴹ Apr 13 '16 at 17:46
  • Please, look my comment bellow here – Flávio Augusto Apr 13 '16 at 18:03
  • You won't see the tables on object explorer until it is refreshed. – Kenneth Fisher Apr 13 '16 at 18:55
0

The first block of code runs in SQL 2012 as

CREATE TABLE uf
(
id int primary key,
uf varchar(2) NOT NULL 
);

Not Null was added to prevent multiple null entries Insert code to fill the table:

insert into uf values (1,'CT')
insert into uf values (2,'VT')
insert into uf values (3,'CA')
insert into uf values (4,'DC')

A foreign key relation prevents updates or inserts of values into the other two tables that are not in the first one.

ALTER TABLE carta 
ADD CONSTRAINT FK_uf FOREIGN KEY (UF) 
    REFERENCES uf (uf) ;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.