Tag Info

Hot answers tagged

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 ...


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

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, ...


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 ...


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 ...


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 ...


1

You will be unable to delete anything from the referencing table as whatever happens to one part of the key happens to the others (ie: SET NULL, SET DEFAULT, etc) and that will trip the constraint if one needs to be set NULL and the other left as-is. I had the same problem and the work-around is to have a BEFORE DELETE trigger on the referenced table. In ...



Only top voted, non community-wiki answers of a minimum length are eligible