The real error here is the first one, of course.
#Postgresql appily 2 primary keys code
If I re-run this code I get the output: ERROR: relation "word" already existsĮRROR: multiple primary keys for table "word" are not allowed
However, the code you show doesn't produce the error you mentioned: $ psql -U postgres regress <<_END_ĪLTER TABLE public.word OWNER TO postgres ĪLTER TABLE ONLY word ADD CONSTRAINT "ID_PKEY" PRIMARY KEY (word,id) Īt a guess you've actually already defined this table and you're ignoring the previous errors, then showing only the last one. But you can't have more than one primary key for a table. You can have a primary key that contains multiple columns (a composite primary key). You can have multiple unique constraints. It's an oxymoron - the definition of a primary key is that it's the primary key, singular. There is a very specific situation in which your primary key can be unique, and the other causes of this problem don't apply to your query but this error still occurs.How can i use multiple primary keys in postgres ? What if none of the listed causes apply, but the error still occurs? To resolve this problem, add the primary key from view B to the problem measure with the sql_distinct_key parameter. (To become familiar with fanouts, check out our related blog entry.) If your query involves a fanout, that may not be the correct primary key to use. In this situation Looker will use the primary key from view A to calculate that measure. Your query may use a measure from view A, but the measure references a field from view B. Referencing fields across views with fanouts If any of the measures in your query are of the type sum_distinct, there may be a uniqueness mismatch between the sql_distinct_key and sql parameters of that measure.Ĭheck out our sum_distinct documentation page for these parameters' requirements. In MySQL, you can use a variable that iterates every row to achieve the same effect:ĭerived_table := + 1 AS UNSIGNED) AS prim_key, t.* Row_number() OVER(ORDER BY created_at) AS prim_key,
This field can then be used as a primary key:
If you receive this error with a derived table, you can use the row_number() window function in Postgres and Redshift databases to make a unique field. Using row_number to generate a primary key for a derived table If no single dimension contains entirely unique values, you may need to concatenate fields to create your own primary key dimension. You will need to choose or create a new dimension as your primary key. If the counts do not match, the primary key is not unique and appears in multiple rows. If the counts in this query match, the primary key is unique. Once you identify the primary key dimensions in your query, you can test them for uniqueness in Looker's SQL Runner with this query: You specify a primary key by using primary_key: yes on a dimension, and it must be a dimension without any repeated values. The most common cause of this error is that your query involves a non-unique primary key.
0 kommentar(er)
