Database Administrators Stack Exchange
Database Administrators Stack Exchange

Most Popular

more tags
1500 questions
22
votes
4 answers

Where should you define foreign keys?

Is it better to define foreign keys in the database or in the code part of an application?
newuser
  • 679
  • 1
  • 9
  • 21
22
votes
2 answers

Non-integer primary key considerations

Context I'm designing a database (on PostgreSQL 9.6) which will store data from a distributed application. Due to the application's distributed nature, I can not use auto-increment integers (SERIAL) as my primary key because of potential…
Renato Massaro
  • 329
  • 2
  • 5
22
votes
1 answer

NOT NULL if value is true in another column

I have a similar table to this one create table my_table ( id serial, attribute boolean, number integer ) Is there a way to have it force not null in column number IF attribute value is true? So if a record is saved with attribute value 'true'…
geogrow
  • 384
  • 2
  • 3
  • 10
22
votes
1 answer

What is the special database "postgres" for?

I have a PostgreSQL server with several databases running on it. One of the databases is called postgres, and it was there right from the beginning. I could not see any tables inside and I've never used it explicitly, but I noticed, that any user is…
Roman Kiselev
  • 323
  • 2
  • 6
22
votes
3 answers

What are the best practices regarding lookup tables in relational databases?

Lookup tables (or code tables, as some people call them) are usually a collection of the possible values that can be given for a certain column. For example, suppose we have a lookup table called party (meant to store information about political…
Nishant
  • 899
  • 2
  • 13
  • 20
22
votes
2 answers

PostgreSQL: Pass table as argument in function

I am discovering TYPE in PostgreSQL. I have a TABLE TYPE that some table must respect (interface). For example: CREATE TYPE dataset AS( ChannelId INTEGER ,GranulityIdIn INTEGER ,GranulityId INTEGER ,TimeValue TIMESTAMP ,FloatValue…
jlandercy
  • 367
  • 1
  • 4
  • 10
22
votes
3 answers

Best design to reference multiple tables from single column?

Proposed schema First and foremost, here is an example of my proposed schema to reference throughout my post: Clothes ---------- ClothesID (PK) INT NOT NULL Name VARCHAR(50) NOT NULL Color VARCHAR(50) NOT NULL Price DECIMAL(5,2) NOT NULL BrandID…
youngrrrr
  • 323
  • 1
  • 2
  • 6
22
votes
3 answers

Is there a penalty for using BINARY(16) instead of UNIQUEIDENTIFIER?

I've recently inherited a SQL Server database that uses BINARY(16) instead of UNIQUEIDENTIFIER to store Guids. It does this for everything including primary keys. Should I be concerned?
Jonathan Allen
  • 3,612
  • 7
  • 25
  • 25
22
votes
4 answers

How can I drop all triggers in a single database?

I have a database with 104 triggers, is there a way to delete all the triggers with a single command from a single database called 'system_db_audits?
Mohamed Mahyoub
  • 363
  • 1
  • 2
  • 7
22
votes
3 answers

Huge data and performance in SQL Server

I've written an application with a SQL Server backend that collects and stores and extremely large amount of records. I've calculated that, at the peak, the average amount of records is somewhere in the avenue of 3-4 billion per day (20 hours of…
Brandon
  • 618
  • 2
  • 7
  • 17
22
votes
1 answer

Meaning of 'locks rec but not gap waiting' in deadlock report

About the meaning of locks rec but not gap waitingin TRANSACTION(1), which one is correct? Already granted gap lock, waiting for clustered index X lock? Already granted clustered index X lock, waiting for gap lock? There are 31 rows in…
Ryan Lyu
  • 523
  • 2
  • 4
  • 14
22
votes
6 answers

Calculate row size and max row size for a table

Is there any way of calculating the number of bytes occupied by the table? I know that you can get some information from information_schema.tables but that information is not accurate enough. What actually required is the number of bytes according…
Nawaz Sohail
  • 1,480
  • 3
  • 10
  • 25
22
votes
1 answer

PostgeSQL 9.3: Is it safe to stop VACUUM FULL?

I'm using PostgreSQL 9.3 on RDS. Once in a while, I run a VACUUM FULL operation on the database. However, such operation can take quite a while and it blocks other tables, so the need to stop the operation might arise. Is it safe to stop a VACUUM…
Adam Matan
  • 12,079
  • 30
  • 82
  • 96
22
votes
2 answers

Does an empty column value occupy same storage space as a filled column value?

I have a table with 2 columns. The type of both columns is set to varchar(38). If I create a row with an empty value for one of the columns, will it take same storage space as if the value was not empty? In other words, will MySQL reserve storage…
Haris ur Rehman
  • 353
  • 1
  • 3
  • 7
22
votes
1 answer

Use of log_bin_trust_function_creators in MySQL

What is the use of log_bin_trust_function_creators? Also, Is the value of the parameter dynamic? Will it change if I restart my MySQL service?
tesla747
  • 1,910
  • 10
  • 42
  • 77
1 2 3
…
99 100