Tag Info

Hot answers tagged

4

SELECT set_config('search_path', 'fred,'||current_setting('search_path'), false); The false says it's not a transaction-LOCAL setting. For the bonus question, you can store the value in a custom setting: SELECT set_config('tmp.search_path', current_setting('search_path'), false); From version 9.2 on, you don't even have to define this setting in ...


3

The answer is a little bit like the old joke: "Doctor, doctor! It hurts when I do this." If you want a foreign key to an entity then that entity needs to have a unique key defined to be referenced. The fact that your Expectations table has a unique candidate key in Expectations.ExpectationId should not prevent you from defining another candidate key that ...


2

The basic answer is: No. Do you have a record of the 18-20 digit value somewhere? In another location in your database, on a piece of paper on you desk, etc? If so then use the source information to correct your error. You have not indicated which database software you are using, which can make a difference for many issues.


2

I would go with a traditional set-based approach for this. Create entity, list, entityList, and listItem tables to store your data. Individual list and entity tables allow you to create lists without entities and vice versa. Constraints on these tables such as unique constraints and foreign keys will help preserve the integrity of the data. For the list, ...


1

You can REVOKE access from that schema, but I am affraid the application will have some unwanted effects. PostgreSQL has fine tuned permission administration. And later you can GRANT access to that schema.


1

You could probably use an XML datatype to store the lists. This way, you'd preserve the ordering as well. Haven't done any extensive performance testing, but with typed XML (a schema) and an XML index on the column, this might work for you. As for insert performance, one advantage of this approach is that you only have to make an insert into a single table, ...


1

You can create a new schema and expose the objects you need with a synonym: USE master; GO IF DB_ID('testDatabase') IS NOT NULL BEGIN ALTER DATABASE testDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE testDatabase END GO CREATE DATABASE testDatabase GO USE testDatabase; GO -- test table in schema dbo CREATE TABLE dbo.test ( ...


1

ALTER SESSION SET CURRENT_SCHEMA will work, but you still need to do all of the grants. – Phil Oct 29 at 23:33



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