how can i use multiple primary keys in postgres ?
You can’t. It’s an oxymoron — the definition of a primary key is that it’s the primary key, singular. You can’t have more than one.
You can have multiple unique constraints. You can have a primary key that contains multiple columns (a composite primary key). But you can’t have more than one primary key for a table.
However, the code you show doesn’t produce the error you mentioned:
$ psql -U postgres regress <<__END__
CREATE TABLE word(
word CHARACTER VARYING NOT NULL,
id BIGINT NOT NULL,
repeat INTEGER NOT NULL
);
ALTER TABLE public.word OWNER TO postgres;
ALTER TABLE ONLY word ADD CONSTRAINT "ID_PKEY" PRIMARY KEY (word,id);
__END__
CREATE TABLE
ALTER TABLE
ALTER TABLE
$
At a guess you’ve actually already defined this table and you’re ignoring the previous errors, then showing only the last one. If I re-run this code I get the output:
ERROR: relation "word" already exists
ALTER TABLE
ERROR: multiple primary keys for table "word" are not allowed
The real error here is the first one, of course.
I strongly recommend always using -v ON_ERROR_STOP=1 in psql, e.g.:
$ psql -v ON_ERROR_STOP=1 -U postgres regress <<__END__
CREATE TABLE word(
word CHARACTER VARYING NOT NULL,
id BIGINT NOT NULL,
repeat INTEGER NOT NULL
);
ALTER TABLE public.word OWNER TO postgres;
ALTER TABLE ONLY word ADD CONSTRAINT "ID_PKEY" PRIMARY KEY (word,id);
__END__
ERROR: relation "word" already exists
$
See how it stops at the first error?
(It’d be the default but that’d break backward compatibility).
Django 1.11 with PostgreSQL.
I go to migrate my site and models.py throws the error that I can’t have more than one primary key. I can’t see where I do (or I’m not understanding how).
class Employee(models.Model):
Aegis_ID = models.UUIDField(primary_key=True, null=False, default=uuid.uuid4, editable=False, serialize=True)
Employee_Number = models.ForeignKey('self', on_delete=models.CASCADE, related_name='Company_Employee_Number',
null=True, blank=True, max_length=6, help_text="Employee ID")
Employee_FName = models.CharField(null=True, blank=True, max_length=25, help_text="First Name")
Employee_LName = models.CharField(null=True, blank=True, max_length=25, help_text="Last Name")
Employee_Email = models.EmailField(max_length=80, blank=True, help_text="GPM Email address")
Employee_Position = models.ForeignKey('self', on_delete=models.CASCADE, null=True, blank=True,
related_name='Department_Employee_Position', max_length=3,
choices=EMPLOYEE_POSITION, help_text="Select position of this Employee.")
Hire_Date = models.DateField(null=True, blank=True, help_text="Enter the employee hire date.")
Employee_Division = models.CharField(max_length=2, null=True, blank=True, choices=DIVISION_CHOICES,
help_text="Assign the Audit Division for this employee.")
Employee_Region = models.CharField(max_length=3, null=True, blank=True, choices=REGION_CHOICES,
help_text="Assign the Audit Region for this employee.")
Employee_District = models.CharField(max_length=3, null=True, blank=True, choices=DISTRICT_CHOICES,
help_text="Assign the Audit District for this Employee.")
Reading the Django pages on this exact topic, it’s listed as a problem resolved in 1.7 and had to do with how Django sorted the tables by class, alphabetically.
I’ve also tried python manage.py flush followed by makemigrations prior to migrate
So, what fields is Django / Postgres attempting to make an «id» and «primary» because I’m just not understanding, here…
According to the Django documentation regarding Automatic Primary Keys, there’s the unseen is id = models.AutoField(primary_key=True) but I also understood that if you assign the primary_key=True to a field, this did not apply
how can i use multiple primary keys in postgres ?
You can’t. It’s an oxymoron — the definition of a primary key is that it’s the primary key, singular. You can’t have more than one.
You can have multiple unique constraints. You can have a primary key that contains multiple columns (a composite primary key). But you can’t have more than one primary key for a table.
However, the code you show doesn’t produce the error you mentioned:
$ psql -U postgres regress <At a guess you’ve actually already defined this table and you’re ignoring the previous errors, then showing only the last one. If I re-run this code I get the output:
ERROR: relation "word" already exists
ALTER TABLE
ERROR: multiple primary keys for table "word" are not allowed
The real error here is the first one, of course.
I strongly recommend always using -v ON_ERROR_STOP=1 in psql, e.g.:
$ psql -v ON_ERROR_STOP=1 -U postgres regress <See how it stops at the first error?
(It’d be the default but that’d break backward compatibility).
Running the database update (update.php) after upgrade from release 7.44 to 7.50 gave me the following message. Website stopped responding normaly sometime after that, we had to restore a backup.
——————————-
The following updates returned messages
node module
Update #7016
Failed: PDOException: SQLSTATE[42P16]: Invalid table definition: 7 ERROR: multiple primary keys for table «history» are not allowed: ALTER TABLE {history} ADD PRIMARY KEY (uid,nid); Array ( ) in db_add_primary_key() (regel 2927 van /srv/Http_Server/Parkstadveendam-nl/drupal/includes/database/database.inc).
——————————-
Log message:
——————————-
TYPE update
DATUM dinsdag 12 juli 2016 21:51
GEBRUIKER Redactie A
LOCATIE http://www.parkstadveendam.nl/update.php?op=selection&token=y0HAIYjz1j-z…
DOORVERWIJZER http://www.parkstadveendam.nl/update.php?op=selection&token=y0HAIYjz1j-z…
BERICHT PDOException: SQLSTATE[42P16]: Invalid table definition: 7 ERROR: multiple primary keys for table «history» are not allowed: ALTER TABLE {history} ADD PRIMARY KEY (uid,nid); Array ( ) in db_add_primary_key() (regel 2927 van /srv/Http_Server/Parkstadveendam-nl/drupal/includes/database/database.inc).
ERNST fout
HOSTNAAM 46.244.50.21
BEWERKINGEN
——————————-
RDBMS is PostgreSQL 9.5
Tag1 supports the Drupal Project.
Здравствуйте. При восстановлении базы и dump, возникли ошибки, как их правильно исправить?
Может можно как-то подправить базу с которой делается dump? Спасибо.
КОНТЕКСТ: COPY dishes, line 1
pg_restore: [архиватор (БД)] Ошибка из записи оглавления 2769; 0 35095 TABLE DATA institutions postgres
pg_restore: [архиватор (БД)] сбой команды COPY для таблицы «institutions»: ERROR: duplicate key value violates unique constraint «institutions_pkey»
ПОДРОБНОСТИ: Key (id)=(6) already exists.
…
pg_restore: [архиватор (БД)] Ошибка из записи оглавления 2539; 2606 36198 CONSTRAINT dishes dishes_pkey postgres
pg_restore: [архиватор (БД)] could not execute query: ERROR: multiple primary keys for table «dishes» are not allowed
Выполнялась команда: ALTER TABLE ONLY dishes
ADD CONSTRAINT dishes_pkey PRIMARY KEY (id);
…
ПОДРОБНОСТИ: Key (product_id)=(118) is not present in table «products».
Выполнялась команда: ALTER TABLE ONLY io_correction_products
ADD CONSTRAINT fk_rails_daec94aa5d FOREIGN KEY (product_id) REFERENCES products(…
pg_restore: [архиватор (БД)] Ошибка из записи оглавления 2586; 2606 36668 FK CONSTRAINT children_groups fk_rails_f9754c73dc postgres
pg_restore: [архиватор (БД)] could not execute query: ERROR: insert or update on table «children_groups» violates foreign key constraint «fk_rails_f9754c73dc»
…
ПРЕДУПРЕЖДЕНИЕ: при восстановлении проигнорировано ошибок: 34
Issue summary
I have a project with wagtail, i tryed to add multiple language page and i installed this package. Following the steps i add the middleware and the package in installed_apps. Add mixin in my models and make migrations. When i try to migrate i have ever this error: "Multiple primary keys are not allowed for the table «blog_blogindexpage»".
How to reproduce?
Install package, put mixins, make migration, and try to migrate.
And example of the mixin:
class BlogIndexPage(TranslatablePage, MetadataPageMixin, Page):
Technical details
Bash log:
./manage.py makemigrations ✓ 4857 02:08:02 You are trying to add a non-nullable field 'translatablepage_ptr' to blogindexpage without a default; we can't do that (the database needs something to populate existing rows). Please select a fix: 1) Provide a one-off default now (will be set on all existing rows with a null value for this column) 2) Quit, and let me add a default in models.py Select an option: 1 Please enter the default value now, as valid Python The datetime and django.utils.timezone modules are available, so you can do e.g. timezone.now Type 'exit' to exit this prompt >>> Please enter some code, or 'exit' (with no quotes) to exit. >>> '' You are trying to add a non-nullable field 'translatablepage_ptr' to blogpage without a default; we can't do that (the database needs something to populate existing rows). Please select a fix: 1) Provide a one-off default now (will be set on all existing rows with a null value for this column) 2) Quit, and let me add a default in models.py Select an option: 1 Please enter the default value now, as valid Python The datetime and django.utils.timezone modules are available, so you can do e.g. timezone.now Type 'exit' to exit this prompt >>> '' Migrations for 'blog': blog/migrations/0008_auto_20170112_1808.py: - Add field translatablepage_ptr to blogindexpage - Add field translatablepage_ptr to blogpage - Alter field page_ptr on blogindexpage - Alter field page_ptr on blogpage salahaddin@TulipanROG ~/Proyectos/uzman/uzman translate ● ? (uzman) ./manage.py migrate ✓ 4858 02:08:36 Operations to perform: Apply all migrations: about, admin, auth, axes, blog, contenttypes, core, dashboard, flatpages, home, jet, marketing, person, robots, services, sessions, sites, standard, taggit, wagtail_embed_videos, wagtailadmin, wagtailcore, wagtaildocs, wagtailembeds, wagtailforms, wagtailimages, wagtailmetadata, wagtailredirects, wagtailsearch, wagtailsearchpromotions, wagtailtrans, wagtailusers, work Running migrations: Applying blog.0008_auto_20170112_1808...Traceback (most recent call last): File "/home/salahaddin/Proyectos/uzman/lib/python3.6/site-packages/django/db/backends/utils.py", line 64, in execute return self.cursor.execute(sql, params) psycopg2.ProgrammingError: no se permiten múltiples llaves primarias para la tabla «blog_blogindexpage» The above exception was the direct cause of the following exception: Traceback (most recent call last): File "./manage.py", line 12, in <module> execute_from_command_line(sys.argv) File "/home/salahaddin/Proyectos/uzman/lib/python3.6/site-packages/django/core/management/__init__.py", line 367, in execute_from_command_line utility.execute() File "/home/salahaddin/Proyectos/uzman/lib/python3.6/site-packages/django/core/management/__init__.py", line 359, in execute self.fetch_command(subcommand).run_from_argv(self.argv) File "/home/salahaddin/Proyectos/uzman/lib/python3.6/site-packages/django/core/management/base.py", line 294, in run_from_argv self.execute(*args, **cmd_options) File "/home/salahaddin/Proyectos/uzman/lib/python3.6/site-packages/django/core/management/base.py", line 345, in execute output = self.handle(*args, **options) File "/home/salahaddin/Proyectos/uzman/lib/python3.6/site-packages/django/core/management/commands/migrate.py", line 204, in handle fake_initial=fake_initial, File "/home/salahaddin/Proyectos/uzman/lib/python3.6/site-packages/django/db/migrations/executor.py", line 115, in migrate state = self._migrate_all_forwards(state, plan, full_plan, fake=fake, fake_initial=fake_initial) File "/home/salahaddin/Proyectos/uzman/lib/python3.6/site-packages/django/db/migrations/executor.py", line 145, in _migrate_all_forwards state = self.apply_migration(state, migration, fake=fake, fake_initial=fake_initial) File "/home/salahaddin/Proyectos/uzman/lib/python3.6/site-packages/django/db/migrations/executor.py", line 244, in apply_migration state = migration.apply(state, schema_editor) File "/home/salahaddin/Proyectos/uzman/lib/python3.6/site-packages/django/db/migrations/migration.py", line 129, in apply operation.database_forwards(self.app_label, schema_editor, old_state, project_state) File "/home/salahaddin/Proyectos/uzman/lib/python3.6/site-packages/django/db/migrations/operations/fields.py", line 84, in database_forwards field, File "/home/salahaddin/Proyectos/uzman/lib/python3.6/site-packages/django/db/backends/postgresql/schema.py", line 21, in add_field super(DatabaseSchemaEditor, self).add_field(model, field) File "/home/salahaddin/Proyectos/uzman/lib/python3.6/site-packages/django/db/backends/base/schema.py", line 409, in add_field self.execute(sql, params) File "/home/salahaddin/Proyectos/uzman/lib/python3.6/site-packages/django/db/backends/base/schema.py", line 112, in execute cursor.execute(sql, params) File "/home/salahaddin/Proyectos/uzman/lib/python3.6/site-packages/django/db/backends/utils.py", line 79, in execute return super(CursorDebugWrapper, self).execute(sql, params) File "/home/salahaddin/Proyectos/uzman/lib/python3.6/site-packages/django/db/backends/utils.py", line 64, in execute return self.cursor.execute(sql, params) File "/home/salahaddin/Proyectos/uzman/lib/python3.6/site-packages/django/db/utils.py", line 94, in __exit__ six.reraise(dj_exc_type, dj_exc_value, traceback) File "/home/salahaddin/Proyectos/uzman/lib/python3.6/site-packages/django/utils/six.py", line 685, in reraise raise value.with_traceback(tb) File "/home/salahaddin/Proyectos/uzman/lib/python3.6/site-packages/django/db/backends/utils.py", line 64, in execute return self.cursor.execute(sql, params) django.db.utils.ProgrammingError: no se permiten múltiples llaves primarias para la tabla «blog_blogindexpage»
Package list:
beautifulsoup4 (4.5.3)
boto (2.45.0)
decorator (4.0.10)
Django (1.10.5)
django-axes (2.3.2)
django-debug-toolbar (1.6)
django-disqus (0.5)
django-embed-video (1.1.0)
django-extensions (1.7.5)
django-jet (1.0.4)
django-modelcluster (2.0)
django-node (4.0.0)
django-overextends (0.4.2)
django-redis (4.7.0)
django-robots (2.0)
django-storages (1.5.1)
django-taggit (0.18.3)
django-treebeard (4.1.0)
django-webpack (3.1.1)
djangorestframework (3.5.3)
elasticsearch (5.0.1)
feedparser (5.2.1)
google-api-python-client (1.6.0)
html5lib (0.999999999)
httplib2 (0.9.2)
ipython (5.1.0)
ipython-genutils (0.1.0)
mock (2.0.0)
oauth2client (4.0.0)
olefile (0.44)
optional-django (0.1.0)
pbr (1.10.0)
pexpect (4.2.1)
pickleshare (0.7.4)
Pillow (4.0.0)
pip (9.0.1)
prompt-toolkit (1.0.9)
psycopg2 (2.6.2)
ptyprocess (0.5.1)
pyasn1 (0.1.9)
pyasn1-modules (0.0.8)
Pygments (2.1.3)
pytz (2016.10)
redis (2.10.5)
requests (2.12.4)
rsa (3.4.2)
setuptools (32.3.1)
simplegeneric (0.8.1)
six (1.10.0)
sphinx-me (0.3)
sqlparse (0.2.2)
traitlets (4.3.1)
Unidecode (0.4.19)
uritemplate (3.0.0)
urllib3 (1.19.1)
wagtail (1.8)
wagtail-embed-videos (0.3.0)
wagtail-metadata (0.3.0)
wagtail-modeltranslation (0.5.0a0)
wagtailgmaps (0.2.5)
wagtailtrans (0.1.1)
Wand (0.4.4)
wcwidth (0.1.7)
webencodings (0.5)
wheel (0.30.0a0)
Willow (0.4)