Tag Info

New answers tagged

1

if you are going count the tables in DB then query would be SELECT COUNT(*) from information_schema.tables WHERE table_type = 'base table'


2

SELECT COUNT(*) FROM information_schema.tables WHERE table_type = 'BASE TABLE' --counts all tables SELECT COUNT(*) FROM information_schema.referential_constraints --counts all FK relationships


2

is this what business process of yours? that must be document table : create table document ( `id` int unsigned auto_increment, `title` varchar(128) not null, primary key(id) ); block table create table block ( `id` int unsigned auto_increment, `id_document` int // foreign key to document primary key(id) ); ...


0

you are almost on the right track with a slight misunderstanding. But I have a lot of tables and I don't one to create a trigger on them one by one. This is not correct. You just have to create SERVER LEVEL / DATABASE LEVEL TRIGGER that will take care of the database events that occur on the server instance or database. You can even filter out the ...


0

Thanks to @PieterGeerkins I've started to take a look into double-entry bookkeeping more, which seems to have helped me resolve the original problem. Using one 'account_id' in 'chart_of_accounts' for the jackpot (instead of one 'account_id' for each jackpot time period) allows me to not worry about referencing 'account_id' from the table 'jackpots'. I'll ...


1

First, just to be certain we're covering our basis, this piece of code: EXP FILE = B.DMP OWNER(B) won't work. That's not a properly formatted EXP command. But assuming that you're using a properly formatted command, all that a user really needs is the EXP_FULL_DATABASE role, and they should be able to export any object in the database: SQL> create ...


1

Start with: Create a ChartOfAccounts table with the Account code as Primary Key. Add a Foreign Key constraint to ChartOfAccounts on all tables with an AccountCode field. Use an IsDebit field, not the numeric sign, to distinguish Debits from Credits and reserve negative signs for transction reversals (if used at all). This is necessary in order to generate ...


0

From what I understand of your problem your "duplicity" is fine. If your saying that all of the shops act totally independently then yes in your example shop E and shop D might have the same data but the data is not related so it might look like a duplicate, but I would not consider that a duplicate. Another example, I am answering this question now, but ...


0

How exactly did you set standard_conforming_strings? If you did that in your session, it only applies to that session. For a permanent setting change your postgresql.conf and reload. Also, a pg_dump cannot reveal anything. The setting in the dump only applies to the dump. To see the current setting of your database, run SHOW standard_conforming_strings; ...


1

Grant EXP_FULL_DATABASE privilege to user A and then export. SQL> grant EXP_FULL_DATABASE to A;


1

http://docs.oracle.com/cd/E11882_01/server.112/e22490/original_export.htm#SUTIL2641 If you do not have the system privileges contained in the EXP_FULL_DATABASE role, then you cannot export objects contained in another user's schema.


0

Yes, you will be better off creating a whole separate table to hold the locations of both cable ends unless you can enforce constraints during row creation. The ambiguity mentioned by Michael Green in a comment, about how one ensures that AB is recognized as identical to BA, is the critical issue. If you are in a position to enforce an ordering constraint ...


3

Site1 and site2 can both be FKs, linked to the ID_site PK. This works on any rdbms that i'm aware of. Example Query: SELECT CT.site1, CT.site2, S1.site_description, S2.site_description FROM circuit_table AS CT INNER JOIN site_table as S1 ON CT.site1 = S1.ID_site INNER JOIN site_table as S2 ON CT.site2 = S2.ID_site


5

Option 4: Class Table Inheritance This is a design technique used to implement a class/subclass situation. In this situation, attributes often apply only to one subclass, and not to all rows in the table. Visit the tag of the same name over in SO to see a bunch of relevant questions and answers. This has the advantage eliminating NULLS (mostly), ...


0

Assuming you have an ascending index on lower_bound, you could try SELECT * FROM table WHERE lower_bound = ( SELECT MIN(lower_bound) FROM table WHERE lower_bound >= VAL ) This sort of query was used by a commercial product many moons ago. It was used to query a table that stored both current data and ...


3

Assuming destination schema is empty: DECLARE @sql NVARCHAR(MAX) = N''; SELECT @sql += N' ALTER SCHEMA Schema2 TRANSFER Schema1.' + QUOTENAME(t.name) + ';' FROM sys.tables AS t INNER JOIN sys.schemas AS s ON t.[schema_id] = s.[schema_id] WHERE s.name = N'Schema1'; PRINT @sql; -- EXEC sp_executesql @sql; If the destination schema is not empty, ...


4

Execute this for all of the tables: alter schema2 transfer schema1.table1; To programmatically get the statements to execute: declare @SourceSchemaName sysname, @DestinationSchemaName sysname, @AlterStatements varchar(max); set @SourceSchemaName = 'NewSchema'; set @DestinationSchemaName = 'HumanResources'; set @AlterStatements = ''; select ...


0

One more option is subtypes where the common columns are stored together and the unique parts are separate. widget (id, date, amount) widget1 (id, *color, *wheelsize, *w1desc5, *w1desc6...*w1desc10) widget2 (id, *power, *warranty, *w2desc5, *w2desc6...*w2desc10) ... widget10 (id, *speed, *w10desc4, *w10desc5, *w10desc6...*w10desc10) Each transaction ...


0

Three approaches suggest themselves. Unless you're really worried about disk space, sparse tables aren't all that great a problem - sure, you'll waste a few bytes, but in these days of Terabyte disks, is it really an issue? You could then construct views on your sparse table for the particular items. You could also create 10 separate tables for your ...


1

There's no single right answer for any performance question. The answer is always that query optimizers are very smart, to a point. So the most efficient design or query will depend on the number of records, what kind of indexes you have, how selective they are, and many, many more factors. If you want to avoid keeping both bounds of your ranges, you can ...


3

The below sample structure illustrates how you can do the TasksTags table most efficiently. The Tasks table enforces unique task names. The Tags table enforces unique tag names. The TasksTags table joins these together allowing any combination of Tasks and Tags. USE tempdb; CREATE TABLE dbo.Tasks ( TaskID INT NOT NULL CONSTRAINT PK_Tasks PRIMARY KEY ...



Top 50 recent answers are included