sql server – Erro 1776, Nível 16

Estou criando um banco e esta mostrando esse erro quando vou criar a tabela disciplina, aonde eu quero fazer uma constraint com a tabela Coordenador.

Mensagem 1776, Nível 16, Estado 0, Linha 56 Não existem chaves
primárias ou candidatas na tabela de referência ‘Coordenador’ que
correspondam à lista de colunas de referência na chave estrangeira
‘FK_Disciplina’. Mensagem 1750, Nível 16, Estado 1, Linha 56 Não foi
possível criar a restrição ou o índice. Consulte os erros anteriores.

Não estou conseguindo encontrar a onde posso estar errando.

CREATE DATABASE AC_02;
GO

USE AC_02
GO

CREATE TABLE Usuario(
  ID INT NOT NULL IDENTITY(1,1)
  , Login VARCHAR(50) NOT NULL UNIQUE
  , Senha VARCHAR(50) NOT NULL
  , DtExpiracao DATETIME DEFAULT ('01/01/1900')
  , CONSTRAINT PK_Usuario PRIMARY KEY ( ID )
)
GO

CREATE TABLE Coordenador(
  ID INT NOT NULL IDENTITY(1,1)
  , id_usuario INT NOT NULL 
  , Nome VARCHAR(100) NOT NULL
  , Email VARCHAR(100) UNIQUE
  , Celular VARCHAR(15) UNIQUE
  , CONSTRAINT FK_Coordenador FOREIGN KEY ( id_usuario ) REFERENCES Usuario ( ID )
)
GO


CREATE TABLE Aluno(
  ID INT NOT NULL IDENTITY(1,1)
  , id_usuario INT NOT NULL 
  , Nome VARCHAR(100) NOT NULL
  , Email VARCHAR(100) UNIQUE
  , Celular VARCHAR(15) UNIQUE
  , RA VARCHAR(20) NOT NULL
  , Foto VARCHAR(100) NULL
  , CONSTRAINT FK_Aluno FOREIGN KEY ( id_usuario ) REFERENCES Usuario ( ID )
)
GO

CREATE TABLE Professor(
   ID INT NOT NULL IDENTITY(1,1)
  , id_usuario INT NOT NULL 
  , Nome VARCHAR(100) NOT NULL
  , Email VARCHAR(100) UNIQUE
  , Celular VARCHAR(15) UNIQUE
  , Apelido VARCHAR(50) NOT NULL
  , CONSTRAINT FK_Professor FOREIGN KEY ( id_usuario ) REFERENCES Usuario ( ID )
)
GO


CREATE TABLE Disciplina(
  ID INT NOT NULL IDENTITY(1,1)
  , Nome VARCHAR(100) UNIQUE
  , Data DATETIME DEFAULT(GETDATE())
  , Status VARCHAR(10) DEFAULT ('Aberta') CHECK(Status IN('Aberta', 'Fechada'))
  , PlanoDeEnsino VARCHAR(500) NOT NULL
  , CargaHoraria INT CHECK(CargaHoraria IN ('40', '80'))
  , Competencias VARCHAR(100) NOT NULL
  , Habilidades VARCHAR(100) NOT NULL
  , Ementa VARCHAR(100) NOT NULL
  , ConteudoProgramatico VARCHAR(100) NOT NULL
  , BibliografiaBasica VARCHAR(100) NOT NULL
  , BibliografiaComplementar VARCHAR(100) NOT NULL
  , PercentualPratico INT CHECK(PercentualPratico IN(0, 100))
  , PercentualTeorico INT CHECK(PercentualTeorico IN(0, 100))
  , idCoordenador INT NOT NULL
  , CONSTRAINT FK_Disciplina FOREIGN KEY ( idCoordenador ) REFERENCES Coordenador ( ID )
)
GO

Import SQL data to sharepoint list using Csom

Stack Exchange Network


Stack Exchange network consists of 176 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

oracle – Why would a scheduled job fail in SQL Developer?

I am trying to create a scheduled job in Oracle SQL Developer that would run once a month and I am testing the Scheduler/Jobs feature where I have created a simple SQL query to test the ability to do this. I have a simple table called “TEST123” and I can see that it works when I do “SELECT * FROM TEST123;”.

In SQL Developer I have a Job called “TEST1” which has a PL/SQL Block = “DROP TABLE TEST123;” to run immediately (though I have also tested this with running at a specific time).

After I see that I can still select from this test table and that the STATE of the job name is “FAILED”. I left all other settings as default. What am I missing here? Why is it failing and is there a way to fix it?

sql server – SQL GroupBy Select min return multiple columns

I have the following sample of a SQL table, which will be filled with around 400 million rows:

enter image description here

I want to be able to return columns Id, UID, STP, A, B, C and then group by the UID and keep the one with the lowest STP values, where a date is in between the StartDate and EndDate (i.e. 23/05/2011)

This is what I have so far:

SELECT Id, UID, STP, A, B, C
FROM (drp-data).(dbo).(schedules)
WHERE StartDate <= CONVERT(date, '2011-05-23') AND EndDate >= CONVERT(date, '2011-05-23')

Microsoft SQL 2019.

sql server – The database is not accessible (ObjectExplorer) on Secondary Node

I have Microsoft SQL Server 2016 (SP2-CU13) AlwaysOn with 2 nodes , on the first node I can execute the queries, but On secondary node when I trying to execute query or just open a DB to see the tables I getting error:

"The Database XX is not accessible. (ObjectExplorer)"

Please advise

Thanks

constraint – Order of foreign keys/cascades processing in SQL Server

I have a parent table and 2 (or more) children tables with foreign key defined with some cascade option. So when an entry from a parent table is being deleted, SQL Server will check 2 (or more) children tables to perform relevant cascade action. The question is – is there a specific order in which SQL Server will process those cascade actions – and, correspondingly, is there an order in which locks are taken on those children rows/pages/tables?

That seems quite relevant for deadlock investigations, however I couldn’t find info in BOL or here.

pivot table – Sql Group By with Max equivalent in Excel

My excel sheet has this data as an example:

type   |make    |price
--------------------
cars   |honda   |500
cars   |acura   |600
cars   |ford    |550
trucks |toyota  |350
trucks |ford    |475
trucks |chevy   |400

I can use a pivot table to group by type and make, and get max of the price, but what I really want is to get the max of the price, and then the single record make associated with that max value, so that the data will ultimately look like:

type   |make    |max of price
--------------------
cars   |acura   |600
trucks |ford    |475

I can easily do this query in sql against a database, but am not finding the correct way to do this in excel.

sqlite – sql producing duplicate column id on join?

Is there any way to only show one id column which is used to join on other tables?

This query produces two id columns used to join each other as primary and foreign key. I only want it to repeat the id column once.

SELECT * FROM products JOIN productimg ON products.id = productimg.id WHERE products.id IN (1, 2, 3)

Query results:

id    name     cid  stk prc    id       url
 1  product1    1   10  3000    1   /static/images/dresses/td1.jpg
 2  product2    2   10  2500    2   /static/images/dresses/td2.jpg
 3  product3    2   4   4000    3   /static/images/dresses/td3.jpg

sql server – Which database to use when you have a 6 billion of rows inside and need to query rows with from list of IDs?

We are currently researching our case of storing the distances between cities. Right now we have 6 billion of those distances.

Our structure right now in SQL Server is that we have a column that represents a float number which represents the relationship between cities.

So here is what I mean, we have a city with ID 1 inside the Locations table and a city with ID 2 inside the same table, a row with distance from 1 to 2 will look like so 1.21000 miles. That column is indexed.

So as an example we need to get the distance from the city 1000 to the city 2535, we need to find 1000.2535 inside the Distances table to get that distance.

Besides that example, we need to select 1000 rows with a generated list of 1000 IDs from those 6 billion rows. What I mean is SELECT id, distance FROM Distances WHERE id IN (1000.2535, 1.2, etc.)

Right now we’ve only tested SQL Server on a local machine and it gives us around 300 ms for such query of 1000 rows, but only when we set a 50 ms timeout (this is needed for a lot of parallel requests from multiple users), if 50 ms timeout is not used it just grows exponentially like 300 ms for the first, 500 ms for the second, 800 ms for the third, etc.

And right now we taking a look at ElasticSearch specifically for mget.

So my questions are:

  1. Which database would you recommend for such a use case?
  2. What would you recommend besides what we’ve thought of maybe some other ideas like splitting into two different columns cities IDs, etc?
  3. What would be the best ways to optimize such a database?

sql server – how to run SQL query without permission

there is a sql query that joins both example1.dbo.table1 and example2.dbo.table2

User1 has no select permission on example1 DB.

User1 has select permission on example2 DB.

User2 can run that query

how can User1 run that query ? I dont want to give select permission to User1.

Is there any possibility of that ?

I created this query as procedure and added “execute as user2” inside of it but still couldn’t run for User1.