New answers tagged

0

This will also give you the referenced table: SELECT TABLE_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS Where COLUMN_NAME like '%id' AND COLUMN_NAME <> 'Id' AND DATA_TYPE = 'int' AND TABLE_NAME + COLUMN_NAME NOT IN (SELECT OBJECT_NAME(fk.parent_object_id) + cpa.name FROM sys.foreign_keys fk ...


1

The answer is, as usual, it depends, in this case, primarily on whether your values are subject to change. Although there are lots of competing principles, the primary principle at play is that in a relational database you want to avoid updating primary (and therefore foreign) key values at all cost. Others might argue that some of the other factors you've ...


0

check the order of sql execution. schema.sql --> images.sql --> data.sql Make sure the image data is actually entered. select * from images; This is because images.sql is not executed because autocommit is not set in some DBs. This is because there is no "commit;" in the last sentence of images.sql. so add the statement "commit;" ...


0

ON UPDATE CASCADE is generally safe (though can be very inefficient) if the parent value is unique and not nullable, for instance a primary yet. If it is both unique and not nullable then there is nothing the cascade can do that can not be undone if it was a mistake. ON DELETE CASCADE is something I consider pretty dangerous so generally avoid. Needing to ...


2

Aside from the aforementioned 'oops' where you delete a record you didn't intend to and now a lot of associated records are gone as well, you probably simply can't cascade delete everything. A thing to keep in mind when using cascades is that it can cause conflicts if there's multiple cascade paths. SQL Server and other DBMS will block the creation of the ...


2

Are there any disadvantages, gotchas, or things to bear in mind when adding ON UPDATE CASCADE ON DELETE CASCADE to every single foreign key in the database? One reason not to : Oops!! If you accidentally delete the wrong record, then you wipe out everything that references on that record. If that record happens to be the one at the root of your entire ...


0

It depends primarily on your business rules, but in general I like to use cascading deletes sparingly because my philosophy is that a database should document facts and be a lasting record. My opinion is definitely biased because I deal with databases where there is a requirement to answer the question "what did we know when." Deletions are rare ...


1

In the Stack Overflow question, What are the Pros and Cons of Cascading delete and updates? The accepted answer by SQLMenace states: Pros: When you delete a row from the Parent table all the foreign key rows are deleted This is usually faster than implementing this with triggers Orphaned rows are unlikely Cons Orphans are possible If by mistake you ...


0

In my case, using mysql 4.6.41, there was no foreign key referring to my unique index, however this error message were thrown and I was not able to delete the index. The index itself was composite one, it used 3 columns; 2 of them were foreign keys on other columns. Once I deleted the foreign keys of these 2 columns I was able to delete the main composite ...


0

I would make the customer ID column be a unique constraint and make the other column be the primary key. One of the database design philosophies is to use non-data columns to enforce referential integrity and to never use them in at the application level. Some of benefits is that you can use an autogenerated sequence for the PK column (to control how they ...


0

It's not complicated to do. You need to create the Reference ID as an alternate key in the Customer table with a unique constraint. Then you can use Reference ID as a foreign key to the Configuration table. I generally try to avoid this however as it can get confusing and harder to maintain the data model.


0

the IF EXISTS (SELECT ... WHERE key = @key) is throwing a Shared lock "s" on table to read. It actually holding the shared lock for the transaction in the SP. On insert the lock gets upgraded to "IX" intent exclusive. The other Sp obtains a "RangeS-S" and needs to Upgrade this or get a new lock "s" . This then causes a ...


0

Here's one sketch. Introduce a generic DEVICES table. CREATE TABLE devices ( device_id ... NOT NULL , device_type ... NOT NULL , ... -- generice attributes , PRIMARY KEY (device_id) , UNIQUE (device_type, device_id) , CHECK (device_type IN ('A','B','C')) ); CREATE TABLE devices_typeA ( device_id ... NOT NULL , device_type ... NOT NULL , ... -- type_A ...


1

You could use a trigger on the log table that rolls back when the object_id being inserted is not found in sys.objects - since triggers are the typical mechanism we use when a foreign key doesn't make sense or isn't possible (like maintaining integrity across database or instance boundaries). But do you really want to lose all of the data because one column ...


Top 50 recent answers are included