Doubt about FK, Visual Studio database

Question

Doubt about FK, Visual Studio database

Navigation

#1 by (2 votes)

4

Hello, I created three tables in a database in Visual Studio, the first two ( clientes and produtos ) are normal tables with primary key in the Nome column. Then I created the table compras that would have dus FK, one for the clientes table and another for produtos indicating the Nome field of each one, thus getting the table code (in Visual):

CREATE TABLE [dbo].[comprar]
(
    [Id] INT NOT NULL PRIMARY KEY, 
    [Data de compra] DATETIME NULL, 
    [Cliente] NCHAR(100) NULL, 
    [Produto] NCHAR(100) NULL, 
    [Entrega] DATETIME NULL, 
    CONSTRAINT [FK_comprar_ToCliente] FOREIGN KEY ([Cliente]) REFERENCES [clientes]([Nome]) ON DELETE CASCADE ON UPDATE CASCADE, 
    CONSTRAINT [FK_comprar_ToProduto] FOREIGN KEY ([Produto]) REFERENCES [produtos]([Nome]) ON DELETE CASCADE ON UPDATE CASCADE
)

But by testing I found that the Cliente and Produto fields of this table still accept any value, even if it is not in the clientes or produtos table. I would like to know how I leave these table fields as a ComboBox or similar, giving the options only the registers registered in the indicated tables? And also how to change records from the customers table and products as soon as it salts a purchase (example, the customer buys 5 tomatoes, hence it decreases the tomato record quantity field in the products table and adds the purchase made to a customer record field in the Customers table)

Customers table

CREATE TABLE [dbo].[clientes] (
    [ID]         INT         IDENTITY (1, 1) NOT NULL,
    [Nome]       NCHAR (150) NOT NULL,
    [CPF]        NCHAR (11)  NULL,
    [Email]      NCHAR (100) NULL,
    [Telefone]   NCHAR (11)  NOT NULL,
    [Endereço]   NCHAR (100) NULL,
    [Cidade]     NCHAR (15)  NOT NULL,
    [Comentário] NCHAR (500) NULL, 
    CONSTRAINT [PK_clientes] PRIMARY KEY ([Nome])
);

Products Table

CREATE TABLE [dbo].[produtos]
(
    [ID] INT NOT NULL IDENTITY , 
    [Nome] NCHAR(150) NOT NULL, 
    [Quantidade] INT NOT NULL, 
    [Marca] NCHAR(100) NOT NULL,  
    [Comentário] NCHAR(500) NULL, 
    CONSTRAINT [PK_produtos] PRIMARY KEY ([Nome])
)

c#

asked by anonymous 05.07.2015 / 18:02

1 answer

How can I check if an object exists in the dictionary and return it What are the advantages of using SplFixedArray instead of an array?

score 2 · Answer 1

Notice that the size of the fields differ by 150 x 100, and being nchar (fixed size) this generates a difference.

Try using the ID as a key in the tables (or a single field like CPF, barcode etc) and use a field of the same type and size as FK, this is the most common practice in modeling

Using the name as the key you would still have another problem, there could be two "Leonardo Villarinho" and your system would only allow a registration.