Tag Info

Hot answers tagged

6

Foreign key constraints are currently implemented with special internal triggers. All of them are run FOR EACH ROW. Note that these are implementation details that can change, so don't rely on it. But the basics have not changed over the last couple of major versions, so major changes are unlikely. I ran a quick test with a simple FK constraint from tbl to ...


4

It does execute N times, and the easiest way to see this is to execute the statement with an EXPLAIN ANALYZE prepended, i. e. EXPLAIN ANALYZE UPDATE a SET col = 1 WHERE othercol = 'foo'; This will give you information similar to this: Trigger for constraint t_col_fk on a: time=1.300 calls=9 (tested with 9.2)


3

Firstly, find out your constraint name thus: select TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where REFERENCED_TABLE_NAME = 'My_Table'; And then remove the constraint by the following means: ALTER TABLE My_Table DROP FOREIGN KEY My_Table_Constraint; Refs: 1 & 2. ...


2

Index Hoarder is telling you that historically this index is not being used. Therefore, the maintenance of the index is a cost in CPU, I/O, and storage, but you are not getting any performance benefit from those indexes. A general policy of considering Foreign Keys for indexes is good general plan, but an index that is not used should most likely be ...


2

Connor MacDonald blogged about this over in NUMBER data type... what harm can it do? as well as Ask Tom: "How do I determine how much storage will be required for NUMBER(p, s)?". In short, it does matter. Take this table: CREATE TABLE T ( x1 number, x2 number(6,3) ); x1 will be 21 bytes while x2 will be 2 bytes. By not specifying the data type, ...


1

I don't have enough reputation to comment on the previous answers. So I thought I'd elaborate a bit. 1) ON DELETE CASCADE means if the parent record is deleted, then any referencing child records are also deleted. ON UPDATE defaults to RESTRICT, which means the UPDATE on the parent record will fail. 2) ON DELETE action defaults to RESTRICT, which means ...



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