Tag Info

New answers tagged

0

My fault - I was convinced that changing the schema owner was also changing the ownership of all tables. But not. solved changing the creation script, so now ownership is given at creation


0

Here's what I eventually came up with. Of course, I'm open to input/refinements from the DBA grandfathers around here! But for now, this approach is giving me the info I need. USE INFORMATION_SCHEMA; Get all tables that have at least one foreign key referencing a column in "mytable" SELECT TABLE_NAME, COUNT(*) AS reference_count FROM ...


0

Foreign key can be multicolumn, so having single value like "REFERENCED_COLUMN" is not possible. Instead the table seems to refer to index covering the referenced column(s), you could try to check these. But there seems to be better possibility with information_schema.KEY_COLUMN_USAGE, which lists directly table/column name and for it referenced ...


0

There is no declarative referential integrity to enforce one or more children in SQL Server. You have to enforce this with application logic. Note too that if you are using a foreign key constraint on the child, you can't save the child until the primary key of the parent is known. This means that you need to have a transactional wrapper (BEGIN TRAN / ...


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


0

You are missing a webserver_id column in the TEST table. CREATE TABLE test(id integer primary key AUTOINCREMENT , page varchar(10) , dp_record_id integer , webserver_id integer , foreign key (webserver_id) REFERENCES target_dp(id)); Works just fine.



Top 50 recent answers are included