Neste artigo tentaremos considerar tudo relacionado às chaves em SQL: para que servem a criação e restrições de chaves? Em geral: vai ser chato 😉

O plano para hoje é:

Na teoria do banco de dados relacional - chaves Estas são certas entidades criadas para estabelecer certas restrições que mantêm a integridade e disponibilidade dos dados nas tabelas do banco de dados.

Em termos simples, as chaves estão em SQL são criados para indicar funcionalidade adicional para uma coluna. Seja a exclusividade ou o fato da coluna fazer referência a outra tabela (chave estrangeira).

Chave primária

Uma coluna que deve ser exclusiva no banco de dados é marcada com uma chave primária. Chave primária ou chave primária significa que o valor da coluna da chave primária não pode ser repetido na tabela. Assim, esta chave permite identificar de forma única um registro na tabela sem medo de que o valor da coluna se repita. Apenas um exemplo: digamos que você tenha uma tabela de usuários. Esta tabela possui os seguintes campos: nome completo, ano de nascimento, telefone. Como identificar um usuário? Parâmetros como nome completo e número de telefone não são confiáveis. Afinal, podemos ter vários usuários não só com o mesmo sobrenome, mas também com o mesmo nome. O número de telefone pode mudar com o tempo e o usuário com o número de telefone pode não ser o que está em nosso banco de dados.

É por isso que a chave primária foi inventada. Uma vez atribuído um identificador exclusivo e pronto. EM meuSql no exemplo do qual realizamos todos os exemplos do campo AUTO_INCREMENT não pode ser definido a menos que você indique que esta é uma chave primária.

Não creio que valha a pena mencionar que um campo marcado como chave primária não pode ficar vazio na hora de criar um registro.

Chave estrangeira ( chave estrangeira)

Existem mais chave estrangeira (chave estrangeira). Também é chamado de referência. É necessário vincular tabelas.

Se você observar a figura acima, a chave estrangeira será o campo do fornecedor na tabela de sapatos. Normalmente, ao criar uma tabela, você especifica uma coluna de valores inteiros exclusivos. Como fizemos quando criamos a tabela fornecedor

Coluna ID_do_fornecedor será exclusivo para cada entrada. Seu valor aparecerá na coluna fornecedor na mesa sapato. Sugiro ver imediatamente um exemplo de como uma chave estrangeira é criada.

Criando uma chave estrangeira

criar sapatos de tabela (shoes_id int chave primária de incremento automático, texto do título, tamanho int, flutuação de preço, contagem int, tipo varchar (30), fornecedor int, chave estrangeira (fornecedor) faz referência ao fornecedor (supplier_id));

Como você pode ver no exemplo acima, a sintaxe para criar uma chave estrangeira é bastante simples. Você precisa adicionar um campo na tabela, e então declarar esse campo como chave estrangeira e indicar para onde ele irá se referir. Neste caso o campo fornecedor se referirá ao campo ID_do_fornecedor na mesa fornecedor

Chave composta (chave composta)

Quanto a uma chave composta, são várias chaves primárias em uma tabela. Assim, tendo criado chave composta, a exclusividade do registro será verificada pelos campos combinados nesta chave.

Existem situações em que, ao inserir em uma tabela, é necessário verificar a exclusividade de um registro usando vários campos ao mesmo tempo. É por isso que uma chave composta foi inventada. Por exemplo, criarei uma tabela simples com chave composta para mostrar a sintaxe:

Criar tabela test(campo_1 int, campo_2 texto, campo_3 bigint, chave primária (campo_1, campo_3));

No exemplo acima, dois campos são combinados em uma chave composta e não haverá registros na tabela com esses campos idênticos.

Isso é tudo sobre as chaves SQL. Este pequeno tutorial é uma preparação para o artigo onde veremos detalhadamente como combinar tabelas para que formem um único banco de dados.

APLICA-SE A: SQL Server (desde 2016)Banco de dados SQL do AzureAzure SQL Data WarehouseParallel Data Warehouse

Você pode definir uma chave primária no SQL Server 2016 usando SQL Server Management Studio ou Transact-SQL. A criação de uma chave primária cria automaticamente um índice clusterizado ou não clusterizado exclusivo correspondente.

Nesta seção

Antes de começar, conclua as etapas a seguir.

Restrições

Segurança

Crie uma chave primária usando:

Estúdio de gerenciamento do SQL Server

Restrições

Uma tabela só pode ter uma restrição de chave primária.

Todas as colunas com uma restrição PRIMARY KEY devem ser NOT NULL. Se a nulidade não for especificada, o sinalizador NOT NULL será definido para todas as colunas com a restrição PRIMARY KEY.

Segurança

Permissões

A criação de uma nova tabela com uma chave primária requer permissão CREATE TABLE no banco de dados e permissão ALTER no esquema no qual a tabela é criada.

A criação de uma chave primária em uma tabela existente requer permissão ALTER na tabela.

Criando uma chave primária

No Object Explorer, clique com o botão direito na tabela à qual deseja adicionar uma restrição exclusiva e selecione Construtor.

EM Designer de mesa Clique no seletor de linha da coluna do banco de dados que você deseja definir como chave primária. Para selecionar várias colunas, pressione e segure a tecla CTRL e clique nos seletores de linha das colunas restantes.

Clique com o botão direito no seletor de linha da coluna e selecione Definir chave primária.

A coluna de origem da chave é identificada pelo símbolo de chave primária no seletor de linha correspondente.

Se a chave primária consistir em mais de uma coluna, pode haver valores duplicados em uma coluna, mas todas as combinações de valores de todas as colunas da chave primária devem ser exclusivas.

Ao definir uma chave composta, a ordem das colunas na chave primária é igual à ordem das colunas mostradas na tabela. No entanto, após a criação da chave primária, a ordem das colunas pode ser alterada. Para obter mais informações, consulte .

Crie uma chave primária em uma tabela existente

EM Explorador de objetos

Crie uma solicitação.

Executar. Este exemplo cria uma chave primária na coluna TransactionID.

USAR AdventureWorks2012; IR ALTER TABLE Production.TransactionHistoryArchive ADD CONSTRAINT PK_TransactionHistoryArchive_TransactionID PRIMARY KEY CLUSTERED (TransactionID); IR

Crie uma chave primária em uma nova tabela

EM Explorador de objetos Conecte-se a uma instância do Mecanismo de Banco de Dados.

No painel padrão, selecione Crie uma solicitação.

Copie o exemplo a seguir na janela de consulta e clique no botão Executar. Este exemplo cria uma tabela e define uma chave primária para a coluna TransactionID.

USAR AdventureWorks2012; IR CREATE TABLE Production.TransactionHistoryArchive1 (TransactionID int NOT NULL , CONSTRAINT PK_TransactionHistoryArchive_TransactionID PRIMARY KEY CLUSTERED (TransactionID)); IR

Para obter mais informações, consulte as seções e .

Apresento a sua atenção uma tradução livre do artigo SQL para Iniciantes Parte 2

É importante que todo desenvolvedor web seja capaz de interagir com bancos de dados. Na segunda parte continuamos aprendendo o idioma SQL e aplicar nossas habilidades ao SGBD MySQL. Exploraremos índices, tipos de dados e consultas mais complexas.

O que você precisa

Consulte a seção "O que você precisa" da primeira parte localizada.

Se você quiser executar esses exemplos em seu servidor, faça o seguinte:

1. Abra seu console MySQL e faça login.
2. Crie o banco de dados “my_first_db” usando a consulta CRIAR, se não tiver sido criado anteriormente.
3. Mude a base usando o operador USAR.

Índices

Índices (ou chaves) são comumente usados ​​para melhorar a velocidade de execução de instruções que selecionam dados (como SELECIONAR) das tabelas.

Eles são uma parte importante de uma boa arquitetura de banco de dados; é difícil classificá-los como “otimização”. Normalmente, os índices são adicionados inicialmente, mas podem ser adicionados posteriormente usando uma consulta ALTERAR TABELA.

Os principais motivos para indexar colunas do banco de dados são:

• Quase todas as tabelas possuem uma chave primária ( CHAVE PRIMÁRIA), geralmente esta é a coluna "id".
• Se uma coluna se destina a armazenar valores exclusivos, ela deve ter um índice exclusivo ( EXCLUSIVO).
• Se você precisar de uma pesquisa frequente em uma coluna (usando-a em uma frase ONDE), deve ter um índice regular ( ÍNDICE).
• Se uma coluna for usada para se relacionar com outra tabela, ela deverá ser uma chave estrangeira, se possível ( CHAVE ESTRANGEIRA) ou um índice regular.

Chave primária (CHAVE PRIMÁRIA)

Quase todas as tabelas possuem uma chave primária, geralmente um número inteiro com uma opção de incremento automático ( AUTO_INCREMET).

As subconsultas geralmente causam degradação significativa no desempenho, portanto, use-as com cuidado.

UNION: Combinando dados

Usando consulta UNIÃO, você pode combinar os resultados de diversas consultas SELECT.

Este exemplo combina estados que começam com a letra “N” com estados com populações maiores:

(SELECT * FROM estados WHERE nome LIKE "n%") UNION (SELECT * FROM estados WHERE população > 10000000);

Observe que Nova York é um estado grande e começa com a letra “N”. No entanto, aparece apenas uma vez na lista, porque duplicatas são removidas automaticamente.

Também a beleza dos pedidos UNIÃOé que eles podem ser usados ​​para unir consultas em tabelas diferentes.

Por exemplo, temos tabelas funcionários (empregados), gerentes (gerentes) e clientes (clientes). Cada tabela possui um campo com um endereço de e-mail. Se quisermos obter todos os endereços de e-mail em uma solicitação, podemos fazer o seguinte:

(SELECT email FROM funcionários) UNION (SELECT email FROM gestores) UNION (SELECT email FROM clientes WHERE inscritos = 1);

Ao preencher esta solicitação, receberemos os endereços de todos os funcionários e dirigentes, e apenas dos clientes inscritos na mailing list.

INSERIR Continuação

Já conversamos sobre o pedido INSERIR no artigo anterior. Agora que vimos os índices, podemos falar sobre recursos adicionais de consulta INSERIR.

INSERIR ... NA ATUALIZAÇÃO DE CHAVE DUPLICADA

Esta é a condição mais comumente usada. Primeiro a solicitação tenta executar INSERIR e se a solicitação falhar devido a um primário duplicado ( CHAVE PRIMÁRIA) ou único ( CHAVE ÚNICA) chave, então a solicitação é executada ATUALIZAR.

Vamos primeiro criar uma tabela de teste.

Esta é uma mesa de armazenamento de alimentos. O campo “estoque” armazena a quantidade de produtos disponíveis em estoque.

Agora vamos tentar inserir um valor existente na tabela e ver o que acontece.

Recebemos um erro.

Digamos que temos uma nova padaria e queremos atualizar o banco de dados, mas não sabemos se já existe uma entrada no banco de dados. Podemos primeiro verificar a existência do registro e depois executar outra consulta de inserção. Ou você pode fazer tudo em uma consulta simples:

Funciona da mesma forma que INSERIR, mas com um recurso importante. Se o registro já existir, ele é excluído e a consulta é executada INSERIR e não receberemos nenhuma mensagem de erro.

Observe, porque Uma linha completamente nova é inserida, o campo de incremento automático é aumentado em um.

Esta é uma forma de evitar a ocorrência de erros de duplicação para manter o aplicativo em execução. Pode ser necessário inserir uma nova linha sem imprimir nenhum erro, mesmo que haja duplicação.

Não há erros ou linhas atualizadas.

Tipos de dados

Cada coluna de uma tabela deve ser de um tipo específico. Já usamos tipos INT, VARCHAR E DATA, mas não se debruçou sobre eles em detalhes. Também veremos mais alguns tipos de dados.

Vamos começar com tipos de dados numéricos. Eu os divido em dois grupos: inteiros e frações.

Todo

Uma coluna inteira só pode armazenar números naturais (sem ponto decimal). Por padrão, eles podem ser positivos ou negativos. Se a opção estiver selecionada NÃO ASSINADO, então apenas números positivos podem ser armazenados.

MySQL suporta 5 tipos de números inteiros de diferentes tamanhos e intervalos:

Tipos de dados numéricos fracionários

Esses tipos podem armazenar números fracionários: FLOAT, DOUBLE e DECIMAL.

FLOAT ocupa 4 bytes, DOUBLE ocupa 8 bytes e é semelhante ao anterior. DUPLO é mais preciso.

DECIMAL(M,N) tem precisão variável. M é o número máximo de dígitos, N é o número de dígitos após a vírgula decimal.

Por exemplo, DECIMAL(13,4) possui 9 casas decimais e 4 casas decimais.

Tipos de dados de string

Você pode adivinhar pelo nome que eles podem armazenar strings.

CHAR(N) pode armazenar N caracteres e possui um valor fixo. Por exemplo, CHAR(50) deve sempre conter 50 caracteres por linha na coluna inteira. O valor máximo possível é 255 caracteres

VARCHAR(N) funciona da mesma forma, mas o intervalo pode variar. N - denota o valor máximo. Se a string armazenada tiver menos de N caracteres, ela ocupará menos espaço no disco rígido. O valor máximo possível é 65.535 caracteres.

Variantes do tipo TEXT são mais adequadas para strings longas. TEXT tem um limite de 65.535 caracteres, MEDIUMTEXT tem um limite de 16,7 milhões e LONGTEXT tem um limite de 4,3 bilhões de caracteres. O MySQL geralmente os armazena em armazenamentos separados no servidor, para que o armazenamento principal seja o menor e mais rápido possível.

.

Conclusão

Obrigado por ler o artigo. SQL é uma linguagem e ferramenta importante no arsenal de um desenvolvedor web.

Chave primária é um campo em uma tabela que identifica exclusivamente cada linha/registro em uma tabela de banco de dados. As chaves primárias devem conter valores exclusivos. A coluna de chave primária não pode ter um valor.

Uma tabela pode ter apenas uma chave primária, que pode consistir em um ou mais campos. Quando vários campos são usados ​​como chave primária, eles são chamados de chave composta.

Se uma tabela tiver uma chave primária definida em qualquer campo(s), então você não poderá ter dois registros com o mesmo valor para esse(s) campo(s).

Observação– Você poderia usar esses conceitos ao criar tabelas de banco de dados.

Criando uma chave primária

Aqui está a sintaxe para definir o atributo ID como chave primária na tabela Clientes.

CRIAR TABELA CLIENTES(ID INT NÃO NULO, NOME VARCHAR (20) NÃO NULO, IDADE INT NÃO NULO, ENDEREÇO ​​CHAR (25), SALÁRIO DECIMAL (18, 2), CHAVE PRIMÁRIA (ID));

Para criar uma restrição de chave primária na coluna "ID" quando a tabela CUSTOMERS já existir, utilize a seguinte sintaxe SQL:

ALTER TABLE CLIENTES ADICIONAM CHAVE PRIMÁRIA (ID);

Observação

Se você usar a instrução ALTER TABLE para adicionar uma chave primária, as colunas da chave primária já devem ter sido declaradas como não contendo valores NULL (se a tabela foi criada primeiro).

Para definir uma chave primária em múltiplas colunas, use a sintaxe SQL abaixo:

CRIAR TABELA CLIENTES(ID INT NÃO NULO, NOME VARCHAR (20) NÃO NULO, IDADE INT NÃO NULO, ENDEREÇO ​​CHAR (25), SALÁRIO DECIMAL (18, 2), CHAVE PRIMÁRIA (ID, NOME));

Para criar uma restrição de chave primária nas colunas ID e NAME quando a tabela CUSTOMERS já existir, use a sintaxe SQL a seguir.

ALTER TABLE CLIENTES ADD CONSTRAINT PK_CUSTID CHAVE PRIMÁRIA (ID, NOME);

Excluindo uma chave primária

Você pode limpar as restrições de chave primária de uma tabela usando a sintaxe fornecida a seguir.

ALTER TABLE CLIENTES DROP CHAVE PRIMÁRIA;

Durante o processo de design do banco de dados, são tomadas decisões sobre quais tabelas devem ser incluídas no banco de dados, quais nomes elas terão (identificadores), quais tipos de dados serão necessários para construir as tabelas e quais usuários terão acesso a cada uma delas. . Além disso, para criar tabelas de forma eficaz, você precisa responder às seguintes perguntas:

• Que tipo e tamanho de colunas constituirão cada uma das tabelas e quais nomes você deve escolher para as colunas da tabela?
• Quais colunas podem conter um valor NULL?
• Eles serão usados? restrições de integridade, valores padrão e regras para colunas?
• A indexação de colunas é necessária, que tipos de índices serão aplicados a colunas específicas?
• Quais colunas serão incluídas nas chaves primária e estrangeira.

Para criar tabelas no ambiente MS SQL Server, use o comando:

<определение_таблицы>::= CRIAR TABELA [nome_banco_de_dados.[proprietário].<элемент_таблицы>| proprietário. ]nome_tabela (

<элемент_таблицы> ::= {<определение_столбца>} | <имя_столбца>[,...n])<выражение> | <ограничение_таблицы>

COMO

<Выражение>Normalmente, o proprietário de uma tabela (dbo) é a pessoa que a criou. define o valor para. coluna calculada Colunas Calculadas define o valor para- são colunas virtuais, ou seja, não são armazenadas fisicamente na tabela e são calculadas a partir dos valores das colunas da mesma tabela. Na expressão para coluna calculada pode haver nomes de colunas regulares, constantes e funções vinculadas por um ou mais operadores. As subconsultas não podem participar de tal expressão. coluna calculada pode ser incluído em uma cláusula SELECT ao especificar uma lista de colunas a serem retornadas como resultado da consulta. não podem ser incluídos em uma chave estrangeira; os valores padrão não são usados ​​para eles; Além do mais, colunas calculadas

<определение_столбца>não pode participar das operações INSERT e DELETE.<тип_данных>::= (nome_coluna<выражение>) [ [ PADRÃO<ограничение_столбца>] | [ IDENTIDADE (início, passo) ]]] [

][...n]] Na definição da coluna preste atenção ao parâmetro IDENTITY, que indica que a coluna correspondente será coluna do contador

. Apenas uma coluna com esta propriedade pode ser definida por tabela. Além disso, você pode especificar o valor inicial e a etapa de incremento. Se esses valores não forem especificados, ambos serão padronizados como 1. Se NOT FOR REPLICATION for especificado com a palavra-chave IDENTITY, o servidor não gerará automaticamente valores para essa coluna, mas permitirá que valores arbitrários sejam inseridos em a coluna. As restrições utilizadas são E restrições de coluna. A diferença entre eles é que restrição de coluna aplica-se apenas a um campo específico e restrição de tabela- para grupos de um ou mais campos.

<ограничение_столбца>::= [ CONSTRAINT nome da restrição ] ( [ NULL | NOT NULL ] | [ (CHAVE PRIMÁRIA | UNIQUE ) [ CLUSTERED | NONCLUSTERED ] [ COM FILLFACTOR = fillfactor ] [ ON (nome do grupo de arquivos | DEFAULT ) ] ] ] | [ [ CHAVE ESTRANGEIRA ] REFERÊNCIAS nome_da_tabela [(nome_da_tabela_gen)] [ON DELETE (CASCADE | SEM AÇÃO)] [ON UPDATE (CASCADE | SEM AÇÃO)] [NÃO PARA REPLICAÇÃO]] | VERIFIQUE [NÃO PARA REPLICAÇÃO](<лог_выражение>) } <ограничение_таблицы>::= ( [ (CHAVE PRIMÁRIA | UNIQUE ) [ CLUSTERED | NONCLUSTERED ] ((nome_coluna [,...n])) ] |CHAVE ESTRANGEIRA[(nome_coluna [,...n])] REFERÊNCIAS nome_tabela [(nome_coluna gen_tabela [ ,...n])] [ ON DELETE ( CASCADE | NO ACTION ) ] [ ON UPDATE ( CASCADE | NO ACTION ) ] | NOT FOR REPLICATION ] | CHECK [ NOT FOR REPLICATION ] (expressão lógica) )

Consideremos parâmetros individuais das estruturas apresentadas associadas a restrições integridade de dados. Restrições de integridade têm precedência sobre gatilhos, regras e padrões. PARA restrições de integridade incluir restrição de chave primária CHAVE PRIMÁRIA restrição de chave estrangeira FOREIGN KEY, restrição UNIQUE, restrição NULL, restrição CHECK.

Restrição de chave primária (PRIMARY KEY)

Uma tabela normalmente possui uma coluna ou combinação de colunas cujos valores identificam exclusivamente cada linha da tabela. Esta coluna (ou colunas) é chamada chave primária tabela e é necessária para garantir sua integridade. Se a chave primária contiver mais de uma coluna, os valores dentro de uma coluna poderão ser duplicados, mas qualquer combinação dos valores de todas as colunas chave primária deve ser único.

Ao criar chave primária O SQL Server cria automaticamente um índice exclusivo nas colunas que fazem parte da chave primária. Acelera o acesso aos dados dessas colunas quando usado chave primária em solicitações.

Uma tabela pode ter apenas uma restrição PRIMARY KEY e nenhuma das colunas incluídas na chave primária pode ser NULL. Ao tentar usá-lo como chave primária coluna (ou grupo de colunas) para a qual restrições de chave primária não forem executados, a chave primária não será criada e o sistema exibirá uma mensagem de erro.

Como a restrição PRIMARY KEY garante que os dados sejam únicos, ela geralmente é definida como colunas de contador. Criação restrições de integridade CHAVE PRIMÁRIA é possível tanto durante a criação quanto quando mudando a mesa. Um dos compromissos chave primáriaé a disposição integridade referencial dados de diversas tabelas. Naturalmente, isto só pode ser implementado definindo chaves estrangeiras correspondentes em outras tabelas.

RESTRIÇÃO DE CHAVE ESTRANGEIRA

Restrição de chave estrangeiraé o principal mecanismo para manter integridade referencial entre tabelas em um banco de dados relacional. Uma coluna da tabela filha especificada como uma chave estrangeira no parâmetro FOREIGN KEY é usada para fazer referência a uma coluna da tabela pai que está nela chave primária. Nome da tabela pai e suas colunas chave primária são especificados na cláusula REFERENCES. Os dados nas colunas definidas como chave estrangeira só podem ter os mesmos valores das colunas associadas a eles chave primária tabela pai. Não é necessário que os nomes das colunas correspondam para vincular as tabelas filha e pai. Uma chave primária pode ser definida em uma coluna com um nome, enquanto uma coluna sujeita a uma restrição FOREIGN KEY pode ter um nome completamente diferente. O único requisito é que as colunas correspondam ao tipo e tamanho dos dados.

Uma chave primária pode ser referenciada não apenas por colunas em outras tabelas, mas também por colunas localizadas na mesma tabela que a própria chave primária; isso permite criar estruturas recursivas.

Uma chave estrangeira pode ser associada não apenas a chave primária outra mesa. Pode ser definido para colunas UNIQUE da segunda tabela ou quaisquer outras colunas, mas as tabelas devem estar no mesmo banco de dados.

Colunas de chave estrangeira podem conter um valor NULL, mas a verificação de restrição FOREIGN KEY é ignorada. A chave estrangeira pode ser indexada, então o servidor encontrará os dados necessários com mais rapidez. A chave estrangeira é definida tanto na criação quanto no trocadores de mesa.

Limitação integridade referencial especifica o requisito de que para cada registro na tabela filho deve haver um registro na tabela pai. Neste caso, fica bloqueada a alteração do valor de uma coluna de relacionamento em um registro da tabela pai na presença de um registro filho, bem como a exclusão do registro pai (proibição de alterações e exclusões em cascata), o que é garantido pelo padrão Parâmetros ON DELETE NO ACTION e ON UPDATE NO ACTION. Para habilitar efeitos em cascata, utilize os parâmetros ON DELETE CASCADE e ON UPDATE CASCADE.