Doubt about FK, Visual Studio database

Asked

Viewed 459 times

4

Hi, I created three tables in a database in Visual Studio, the first two (clientes and produtos) are normal tables with Primary key in column Nome. Then I created the table compras that would have dus FK, one for table clientes and another to produtos indicating the field Nome of each one, thus leaving the code of the table (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 when testing I found that the fields Cliente and Produto this table continues to accept any value, even if it is not in the table clientes or produtos. I wanted to know how I leave these table fields as a Combobox or similar, giving the options only the records registered in the indicated tables? And also how to change table records customers and products as soon as it Salar a purchase (example, customer buys 5 tomatoes, hence it decreases the field of tomato record quantity in table products and adds purchase made to a field of customer record in table customers)

Table customers

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])
);

Table Products

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# database

  • What is the PK of the Customer and Products tables ?

  • There are several questions in one post only... He missed saying or citing that he is using SQL Server, because "Visual Studio" is not a database...you are modeling the BD for it.

  • Database is SQL Server... PK in both tables are the column Name, because the field for FK must be the PK of the other table, and as wanted to be done with Name put Name

  • 1 It is not usual for a field of this type to be PK , the problem may be there. 2 Name content should be exact as it is in Pk 3 It is another thing to check whether the constraints of fk and/or pk are "enabled" Pks and Fks.

  • Post also the description of the other tables

  • I added the code of the other tables, are as enabled.. I saw a video on Youtube and tested in another project and then this, FK is working, but in the program it does not give error message when it does not find record equal to what was indicated in the field cliente or produto and accepts it

Show 1 more comment

1 answer

2

Note 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 as 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 key you would still have another problem , there could be two "Leonardo Villarinho" and your system would only allow a registration.

Browser other questions tagged c# database

You are not signed in. Login or sign up in order to post.