Tag Info

New answers tagged

2

Sorry for waking up this dead thread, but I hate it when questions remains inconclusive. Anyway, I had the same problem. Apparently SQL server replication saves foreign keys in "dbo.MSsavedforeignkeys", which is where I found my blocker FK. A quick delete dbo.MSsavedforeignkeys where constraint_name = N'FK_TableOnlyInDestDB_MyReplicatedTable' solved my ...


1

Taken from the MSDN article on FOREIGN KEY Constraints A FOREIGN KEY constraint does not have to be linked only to a PRIMARY KEY constraint in another table; it can also be defined to reference the columns of a UNIQUE constraint in another table. I think it is perfectly fine to define a UNIQUE CLUSTERED INDEX on an IDENTITY column and define the ...


2

This is likely to be a many to many relationship. create table drivers ( driver_id int primary key, ... ); create table trucks ( truck_id int primary key, ... ); create table drivers_trucks ( driver_id int references drivers(driver_id), truck_id int references trucks(truck_id), primary key (driver_id, truck_id) );


1

Swap the foreign key so that the Drivers table has id_truck. This way multiple drivers can be associated with the same truck.


0

Yes, I would add a surrogate 4-byte integer key. Your current two columns are 100 bytes, this could then be reduced to 58 bytes by the addition of the new identity column. You could even make the surrogate key a 2-byte smallint if you are sure you will never exceed 32,767 categories (might be still a good idea to leave as INT just in case). The space ...


0

VARCHAR column as Primary Key is not a good choice as normally we create Cluster Index on same column. Cluster Index on VARCHAR columns is a bad choice because of expected high fragmentation rate. Every new inserted key value will try to find its place somewhere between existing keys and normally cause page split and high index fragmentation. As a result ...


0

Here is the quick fix: Just add a UNIQUE KEY on tagName in the cardTags table SAMPLE SCRIPT DROP DATABASE IF EXISTS testing; CREATE DATABASE testing; USE testing CREATE TABLE 61furiousFistPokemon( cardNumber int NOT NULL PRIMARY KEY, pokemonName VARCHAR(12), type VARCHAR(10), stage VARCHAR(10), evolvesFrom VARCHAR(12), HP INT, retreatCost INT, weakness ...


1

Foreign key are constraints for data consistency, and they should be added always as a security measure. In order for the foreign keys to be fast, MySQL also creates its corresponding indexes unless the columns are already indexed. That is independently of the performance question: create the foreign keys to avoid storing incorrect data. In most cases, you ...


1

tagName column in table cardTags is not defined as primary key and you are declaring foreign key FOREIGN KEY (tagName) REFERENCES cardTags(tagName).


0

Very often it happens, when the foreign key and the reference key don't have same type or same length. Click here for more details.


1

It looks like your currency_pairs_ticker is joining the wrong fields. your existing join: left join currency_pairs_ticker as T on P.pair_id = T.currency_ticker_id corrected join: left join currency_pairs_ticker as T on P.pair_id = T.currency_pairs_pair_id The complete sql SELECT distinct E.exchange_id, E.exchange_name, P.pair_id, ...


1

Yes, there is an alternative syntax. It's in the MySQL docs. It would be: column_name int references table_name(id), But it is only parsed (and ignored) in MySQL. So, you can't use it.


2

I guess that you mean: "every Nurse is an Employee". This can be done with (keeping the Employee table as it is): CREATE TABLE [dbo].[Employee]( [Id] [int] IDENTITY(1,1) NOT NULL, -- why not name this EmployeeId ? [Name] [nvarchar](100) NOT NULL, [StaffNumber] [nvarchar](50) NOT NULL, [Salary] [money] NOT NULL, [Address] ...


0

Seems all you need to do is to add a primary key to your Nurse table and add a uniqu foreign key relationship from the Nurse table to the Employee table; CREATE TABLE [dbo].[Employee]( [Id] [int] IDENTITY(1,1) NOT NULL, [Name] [nvarchar](100) NOT NULL, [StaffNumber] [nvarchar](50) NOT NULL, [Salary] [money] NOT NULL, [Address] ...



Top 50 recent answers are included