• Sign In
  1. Messages

Problem with FK references core.Users

LabKey Support Forum (Inactive)

View Message

view list print
Problem with FK references core.Users kune  2015-03-03 01:23
Status: Closed
 
Hello am developing client side file based module that defines own schema and i want to point some foreign keys to core schema is it possible?

One of my tables looks like that:

CREATE SCHEMA WorkFlowManagement;

CREATE TABLE WorkFlowManagement.WorkFlows(
    WorkFlowId BIGSERIAL NOT NULL,
    WorkFlowName TEXT NOT NULL UNIQUE ,
    ResponsiblePersonId INTEGER,
    ContainerPath TEXT NOT NULL,
    WorkFlowType TEXT NOT NULL,

    CONSTRAINT PK_WorkFlowId PRIMARY KEY (WorkFlowId),
    CONSTRAINT FK_ResponsiblePersonId FOREIGN KEY (ResponsiblePersonId) REFERENCES core.Users(UserId)
    ON UPDATE NO ACTION
    ON DELETE SET NULL
);


This will end up with 500 error:
ERROR: referenced relation "users" is not a table

It seems like there are no table users. I correctly set dependency on core module in module.xml (checked without db script). schema.xml should be correctly defined too.

Any help will be appreciated.

Thanks Michal.
 
 
adam responded:  2015-03-03 06:59
You can't create an FK to core.Users because it's a view. Its definition in core-create.sql is:

    CREATE VIEW core.Users AS
        SELECT Principals.Name AS Email, UsersData.*, Principals.Active
        FROM core.Principals Principals
            INNER JOIN core.UsersData UsersData ON Principals.UserId = UsersData.UserId
        WHERE Type = 'u';

You probably want to define your FK to core.Principals(UserId) instead.

Adam
 
kune responded:  2015-03-03 07:23
Thank you Adam,
i noticed that every module (not just file based) uses sql scripts to define its schema, now it is clear :)

Michal
 
adam responded:  2015-03-03 07:33
Yes, all modules use the same sql script mechanism; you'll find some good examples in the java modules (and probably a few bad examples).

Adam
 
kevink responded:  2015-03-03 07:55
You can also leave off the constraint and add a FK at the query level within the server using the metadata xml:

    <ns:column columnName="ResponsiblePersonId">
        <ns:columnTitle> Responsible Person</ns:columnTitle>
        <ns:nullable>true</ns:nullable>
        <ns:datatype>userid</ns:datatype>
        <ns:scale>30</ns:scale>
        <ns:fk>
          <ns:fkColumnName>UserId</ns:fkColumnName>
          <ns:fkTable>Users</ns:fkTable>
          <ns:fkDbSchema>core</ns:fkDbSchema>
        </ns:fk>
      </ns:column>
 
kune responded:  2015-03-03 13:18
I didn`t know there is so much possibilities in schema.xml file... is there reference to this file? i found just brief description in module file map. I would naturally prefer DB FK is there any advantages?

Thank for information Michal
 
kevink responded:  2015-03-03 14:06
We don't put enforced constraints on the user columns to make deleting users easier and to preserve the user id value even after the user is deleted. Most of the tables in LabKey have a CreatedBy and ModifiedBy column that don't have an FK to the users table. As an aside: the Owner, Created, CreatedBy, Modified, and ModifiedBy columns will be populated for you when you insert or update a row.

The xml schema reference documents the various metadata available in the schema.xml file:

https://www.labkey.org/download/schema-docs/xml-schemas/schemas/tableInfo_xsd/schema-summary.html

The same metadata is used for custom queries as well:

https://www.labkey.org/wiki/home/Documentation/page.view?name=metadataSql
https://www.labkey.org/wiki/home/Documentation/page.view?name=columnMetadata
 
kune responded:  2015-03-03 20:17
Thank you! I`ll study it.

Michal