Tag Info

New answers tagged

0

I had similar issue, Snapshot that I was trying to apply was in infinite loop. Deleting the record from MSsavedforeignkeys helped. Thanks to Erik.


1

As you cannot use the vendor name as identifier, since it may be duplicated (as it happens with the name of a person), you need a true identifier to be set upon creation. When someone identifies themselves in your system, they cannot identify by their name, since it is not enough to uniquely identify them. Thus you don't have any other option than assigning ...


1

Another approach that seems not to have been mentioned yet is to have Hazards and Tasks use the same ID space. If a Hazard has a Task, it will have the same ID. If a Task is for a Hazard it will have the same ID. You would use a sequence rather than identity columns to populate these IDs. Queries on this type of data model would use (full) outer joins to ...


7

You could go with your own idea of an asymmetric schema by removing one of the foreign keys from the current set-up, or, to keep things symmetric, you could remove both foreign keys and introduce a junction table with a unique constraint on each reference. So, it would be like this: CREATE TABLE dbo.Hazard ( HazardId int IDENTITY(1,1) NOT NULL CONSTRAINT ...


2

To sum up: Hazards have one or zero Tasks Tasks have one or zero Hazards If the Task and Hazard tables are used for something else (i.e. tasks and/or hazards have other data associated, and the model you showed to us is simplified to show only the relevant fields) I'd say that your solution is correct. Otherwise, if Tasks and Hazards only exist to be ...


8

The problem you're facing is that you are not actually re-enabling your constraint (as defined by the NOCHECK). The syntax for re-enabling a constraint is as follows: ALTER TABLE <tablename> WITH { CHECK | NOCHECK } CHECK CONSTRAINT <constraintname>; The WITH CHECK|NOCHECK tells SQL Server whether or not you want to check the existing data in ...


1

You should disable anything related to such keys before dropping them Using the variables FOREIGN_KEY_CHECKS and UNIQUE_CHECKS in your session, run SET FOREIGN_KEY_CHECKS = 0; SET UNIQUE_CHECKS = 0; Doing this in your session makes mysqld bypass referential checks in your session. You are still subject to the usual locks if anyone is accessing the tables ...


0

Just for a bit of background on pros/cons to not having FK's in your database feel free to review this post on StackOverflow. As you can see from that article there are some good reasons (performance penalty on insert/update more painful to the business than dirty data) to go without foreign keys and some bad reasons (laziness/ignorance). I think you ...


0

Do you already have all the Airports? Some of them? None of them? The details depend on which case you are doing. Two steps (per normalization table): INSERT...SELECT DISTINCT ... LEFT JOIN .. WHERE .. IS NULL to add to normalization table. Then UPDATE ... SELECT ... JOIN... to get the ids back. The code here is aimed at the "some of them" case, bu ...


2

You cannot create the contraint on A.actual_id because it may contains NULLs and there is no PK or unique index. I didn't touch B because you said you cannot change it. Here is what I did. It may work for you: Create A_data where <> 0, Create A_null when = 0 and your Table B Create Table dbo.A_data ( full_id INTEGER NOT NULL PRIMARY KEY, ...



Top 50 recent answers are included