Ms SQL Server Cross Table Constraint

MS SQL Server cross table constraint

You can create an indexed view based on the Relation and Screen tables and apply a unique constraint there.

create view DRI_UniqueScreens
with SCHEMABINDING
as
    select r.AppId,s.Name
    from
       [Schema].Relation r
         inner join
       [Schema].Screen s
         on
            r.ScreenId = s.ScreenId
GO
CREATE UNIQUE CLUSTERED INDEX IX_DRI_UniqueScreens
    on DRI_UniqueScreens (AppId,Name)

Cross Table Dependency/Constraint in SQL Database

TL;DR

But the dependency is in a separate table.

You mean there is a dependency (in the everyday sense) on another table. We say there is a constraint on the two tables. (They depend on each other.) In addition to the FK (foreign key) constraint that every students classes value is a classes class value.

What is this dependency called?

We can reasonably categorize the constraint as "inter-table". It is that classes equals SELECT class, SUM(student) AS total FROM classes LEFT JOIN students USING (class) GROUP BY class.

And can we say this is violating 3NF?

The constraint doesn't involve violating a NF. Moreover normalization applies only to a single table and its FDs (functional dependencies).

(A straightforward design is to have base students, base classes1 that is the original classes without total, and VIEW classes AS SELECT class, SUM(student) AS total FROM classes1 LEFT JOIN students USING (class) GROUP BY class.)

If I had a column in classes that stored the total number of students a class has, this feels like it should violate 3NF.

Whether a table is in a given NF (normal form) has nothing to do with any other tables. (We say a database is in a given NF when all its tables are.) Whether your design is nevertheless bad is another matter.

Since a class has just one total number of students, there is a FD (functional dependency) of total on class in classes, ie class functionally determines total.

We say that a set of columns functionally determines another set in a table when each subrow for the first always appears with the same subrow for the second. Normalization to higher NFs replaces a table by projections of it that join back ot it, per the FDs & JDs (join dependencies) that hold in it. There is redundancy in a database when two tables say the same thing about the business/application situation; but not all redundancy is bad. Learn proper information modeling & database design.

It may or may not violate a NF to have your class student count as a column in classes. What FDs violate a NF depends on all the FDs present and the NF. (And it only make sense to talk about a particular FD in a particular table violating a particular NF if you are talking about a particular part of a particular definition of that NF.)

(If a DBMS-calculated/computed/generated column violates a NF that would hold without it then that is not a problem, because it is controlled by the DBMS. You can think of the table as view of the table without the column.)

But the dependency is in a separate table.

When a sequence of database states cannot hold all the values possible per the the columns of tables we say constraints hold or the database is constrained. FDs (functional dependencies), MVDs (multi-valued dependencies), JDs (join dependencies), INDs (inclusion dependencies), EQDs (equality dependencies) and other "dependencies" (which technically are expressions given a context) are each associated with certain constraints. CKs (candidate keys), PKs (primary keys), superkeys (SQL PK & UNIQUE NOT NULL), FKs (foreign keys) (which technically are all column sets) & other notions are also each associated with certain constraints. But arbitrary conditions can hold on a sequence of database states.

SQL has a distinct but related notion of a constraint characterized by a name and an expression/condition (constraint in the above sense), declared by appropriate syntax. A state is constrained by column typing, PK, UNIQUE, NOT NULL & CHECK constraints. ASSERTION gives an arbitrary condition on a state but it is not supported by most DBMSs. CASCADES supports some inter-state inter-table constraints. SQL TRIGGERs enforce arbitrary constraints. Indexes also enforce constraints in a DBMS-specific way.

Because in some sense it has all the problems of a 3NF violation.

Your edits improved your question. Using the wrong words or using words in the wrong way at best states something that is not what we mean. But when what we write doesn't make sense it suggests that our problem, whatever else it involves, involves not knowing what the words mean. Forcing ourselves to use words correctly allows others to know what we really mean. Eg here maybe "... in the join of tables ... there would be a 3NF-violating FD ...". Even by explicitly saying that we are unsure we can communicate some of our vague groping without saying something that we don't mean. Eg your "this feels like ...". But it also leads us to clearly organize what we are faced with. This helps not only the problem we are working on but improves our problem solving.

How to enforce a CHECK constraint across multiple tables

You can create triggers on Breedings table to check this rule. Trigger is a special stored procedure which executed automatically on INSERT\UPDATE\DELETE on some table. So you can write a trigger that checks all new rows inserted in Breedings and if there is a row where Date is less then appropriate BirthDate, throw error. Same for UPDATE, if Date column is altered, check appropriate animal's BirthDate and throw error accordingly. DELETEs are safe in this matter.

CHECKs are not that good for rules that involve other tables. General suggestion is to use them only for basic checks inside one table.

LATER EDIT

Try this trigger body

...
BEGIN
 if exists
 (
  SELECT 1
  FROM inserted i
    join Animals.Females f
      on i.DataID = f.DataID
    join Animals.Master m
      on f.DataID = m.DataID
  WHERE
    m.BirthDate > i.Date

 )
 RAISERROR("Trigger iCheckBreedingDateAfterBirthDate - Breedings.Date is wrong", 18, 0)
END
GO

Difficult Temporal Cross-Table Database Constraint

Don't complicate it, I'd go with triggers. There is no shame in using them, this is what they are there for.

To avoid lots of logic in the triggers, I add an "Editable" bit column into the header table, then basically use a divide with Editable to either work or cause a divide by zero error, which I CATCH and convert to a Invoice is not editable, no changes permitted message. There are no EXISTS used to eliminate extra overhead. Try this:

CREATE TABLE testInvoices
(
     InvoiceID   INT      not null  IDENTITY(1,1) PRIMARY KEY
    ,Editable    bit      not null  default (1)  --1=can edit, 0=can not edit
    ,yourData    char(2)  not null  default ('xx')
)
go

CREATE TABLE TestFees
(
    FeeID     INT IDENTITY(1,1) PRIMARY KEY
   ,InvoiceID INT REFERENCES testInvoices(InvoiceID)
   ,Amount    MONEY
)
go

CREATE TRIGGER trigger_testInvoices_instead_update
ON testInvoices
INSTEAD OF UPDATE
AS
BEGIN TRY
    --cause failure on updates when the invoice is not editable
    UPDATE t 
        SET Editable =i.Editable
           ,yourData =i.yourData
        FROM testInvoices            t
            INNER JOIN INSERTED      i ON t.InvoiceID=i.InvoiceID
        WHERE 1=CONVERT(int,t.Editable)/t.Editable    --div by zero when not editable
END TRY
BEGIN CATCH

    IF ERROR_NUMBER()=8134 --catch div by zero error
        RAISERROR('Invoice is not editable, no changes permitted',16,1)
    ELSE
    BEGIN
        DECLARE @ErrorMessage nvarchar(400), @ErrorNumber int, @ErrorSeverity int, @ErrorState int, @ErrorLine int
        SELECT @ErrorMessage = N'Error %d, Line %d, Message: '+ERROR_MESSAGE(),@ErrorNumber = ERROR_NUMBER(),@ErrorSeverity = ERROR_SEVERITY(),@ErrorState = ERROR_STATE(),@ErrorLine = ERROR_LINE()
        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState, @ErrorNumber,@ErrorLine)
    END

END CATCH
GO

CREATE TRIGGER trigger_testInvoices_instead_delete
ON testInvoices
INSTEAD OF DELETE
AS
BEGIN TRY
    --cause failure on deletes when the invoice is not editable
    DELETE t
    FROM testInvoices            t
        INNER JOIN DELETED       d ON t.InvoiceID=d.InvoiceID
        WHERE 1=CONVERT(int,t.Editable)/t.Editable    --div by zero when not editable
END TRY
BEGIN CATCH

    IF ERROR_NUMBER()=8134 --catch div by zero error
        RAISERROR('Invoice is not editable, no changes permitted',16,1)
    ELSE
    BEGIN
        DECLARE @ErrorMessage nvarchar(400), @ErrorNumber int, @ErrorSeverity int, @ErrorState int, @ErrorLine int
        SELECT @ErrorMessage = N'Error %d, Line %d, Message: '+ERROR_MESSAGE(),@ErrorNumber = ERROR_NUMBER(),@ErrorSeverity = ERROR_SEVERITY(),@ErrorState = ERROR_STATE(),@ErrorLine = ERROR_LINE()
        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState, @ErrorNumber,@ErrorLine)
    END

END CATCH
GO

CREATE TRIGGER trigger_TestFees_instead_insert
ON TestFees
INSTEAD OF INSERT
AS
BEGIN TRY
    --cause failure on inserts when the invoice is not editable
    INSERT INTO TestFees
            (InvoiceID,Amount)
        SELECT
            f.InvoiceID,f.Amount/i.Editable  --div by zero when invoice is not editable
            FROM INSERTED                f
                INNER JOIN testInvoices  i ON f.InvoiceID=i.invoiceID
END TRY
BEGIN CATCH

    IF ERROR_NUMBER()=8134 --catch div by zero error
        RAISERROR('Invoice is not editable, no changes permitted',16,1)
    ELSE
    BEGIN
        DECLARE @ErrorMessage nvarchar(400), @ErrorNumber int, @ErrorSeverity int, @ErrorState int, @ErrorLine int
        SELECT @ErrorMessage = N'Error %d, Line %d, Message: '+ERROR_MESSAGE(),@ErrorNumber = ERROR_NUMBER(),@ErrorSeverity = ERROR_SEVERITY(),@ErrorState = ERROR_STATE(),@ErrorLine = ERROR_LINE()
        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState, @ErrorNumber,@ErrorLine)
    END

END CATCH
GO

CREATE TRIGGER trigger_TestFees_instead_update
ON TestFees
INSTEAD OF UPDATE
AS
BEGIN TRY
    --cause failure on updates when the invoice is not editable
    UPDATE f 
        SET InvoiceID =ff.InvoiceID
           ,Amount    =ff.Amount/i.Editable --div by zero when invoice is not editable
        FROM TestFees                f
            INNER JOIN INSERTED     ff ON f.FeeID=ff.FeeID
            INNER JOIN testInvoices  i ON f.InvoiceID=i.invoiceID
END TRY
BEGIN CATCH

    IF ERROR_NUMBER()=8134 --catch div by zero error
        RAISERROR('Invoice is not editable, no changes permitted',16,1)
    ELSE
    BEGIN
        DECLARE @ErrorMessage nvarchar(400), @ErrorNumber int, @ErrorSeverity int, @ErrorState int, @ErrorLine int
        SELECT @ErrorMessage = N'Error %d, Line %d, Message: '+ERROR_MESSAGE(),@ErrorNumber = ERROR_NUMBER(),@ErrorSeverity = ERROR_SEVERITY(),@ErrorState = ERROR_STATE(),@ErrorLine = ERROR_LINE()
        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState, @ErrorNumber,@ErrorLine)
    END

END CATCH
GO

CREATE TRIGGER trigger_TestFees_instead_delete
ON TestFees
INSTEAD OF DELETE
AS
BEGIN TRY
    --cause failure on deletes when the invoice is not editable
    DELETE f
    FROM TestFees                f
        INNER JOIN DELETED      ff ON f.FeeID=ff.FeeID
        INNER JOIN testInvoices  i ON f.InvoiceID=i.invoiceID AND 1=CONVERT(int,i.Editable)/i.Editable --div by zero when invoice is not editable
END TRY
BEGIN CATCH

    IF ERROR_NUMBER()=8134 --catch div by zero error
        RAISERROR('Invoice is not editable, no changes permitted',16,1)
    ELSE
    BEGIN
        DECLARE @ErrorMessage nvarchar(400), @ErrorNumber int, @ErrorSeverity int, @ErrorState int, @ErrorLine int
        SELECT @ErrorMessage = N'Error %d, Line %d, Message: '+ERROR_MESSAGE(),@ErrorNumber = ERROR_NUMBER(),@ErrorSeverity = ERROR_SEVERITY(),@ErrorState = ERROR_STATE(),@ErrorLine = ERROR_LINE()
        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState, @ErrorNumber,@ErrorLine)
    END

END CATCH
GO

here is a simple test script to test out the different combinations:

INSERT INTO testInvoices VALUES(default,default) --works
INSERT INTO testInvoices VALUES(default,default) --works
INSERT INTO testInvoices VALUES(default,default) --works

INSERT INTO TestFees (InvoiceID,Amount) VALUES (1,111)  --works
INSERT INTO TestFees (InvoiceID,Amount) VALUES (1,1111) --works
INSERT INTO TestFees (InvoiceID,Amount) VALUES (2,22)   --works
INSERT INTO TestFees (InvoiceID,Amount) VALUES (2,222)  --works
INSERT INTO TestFees (InvoiceID,Amount) VALUES (2,2222) --works

update testInvoices set Editable=0 where invoiceid=3 --works
INSERT INTO TestFees (InvoiceID,Amount) VALUES (3,333) --error<<<<<<<

UPDATE TestFees SET Amount=1 where feeID=1 --works
UPDATE testInvoices set Editable=0 where invoiceid=1 --works
UPDATE TestFees SET Amount=11111 where feeID=1 --error<<<<<<<
UPDATE testInvoices set Editable=1 where invoiceid=1 --error<<<<<<<

UPDATE testInvoices set Editable=0 where invoiceid=2 --works
DELETE TestFees WHERE invoiceid=2 --error<<<<<

DELETE FROM testInvoices where invoiceid=2 --error<<<<<

UPDATE testInvoices SET Editable='A' where invoiceid=1 --error<<<<<<< Msg 245, Level 16, State 1, Line 1 Conversion failed when converting the varchar value 'A' to data type bit.

Using unique index for cross table constraint

No, you can't use a unique index to enforce this sort of rule.

You could try to enforce this sort of rule via triggers but that tends to get rather difficult in a multiuser environment. You'd need to do things like lock the department row to ensure that only one session can be modifying employee information for a particular department at a time which tends to introduce significant scalability issues. It also tends to involve quite a bit of code to handle all the potential cases.

Cross table constraints in PostgreSQL

Clarifications

The formulation of this requirement leaves room for interpretation:

where UserRole.role_name contains an employee role name.

My interpretation:

with an entry in UserRole that has role_name = 'employee'.

Your naming convention is was problematic (updated now). User is a reserved word in standard SQL and Postgres. It's illegal as identifier unless double-quoted - which would be ill-advised. User legal names so you don't have to double-quote.

I am using trouble-free identifiers in my implementation.

The problem

FOREIGN KEY and CHECK constraint are the proven, air-tight tools to enforce relational integrity. Triggers are powerful, useful and versatile features but more sophisticated, less strict and with more room for design errors and corner cases.

Your case is difficult because a FK constraint seems impossible at first: it requires a PRIMARY KEY or UNIQUE constraint to reference - neither allows NULL values. There are no partial FK constraints, the only escape from strict referential integrity are NULL values in the referencing columns due to the default MATCH SIMPLE behavior of FK constraints. Per documentation:

MATCH SIMPLE allows any of the foreign key columns to be null; if any
of them are null, the row is not required to have a match in the referenced table.

Solution

This enforces your requirements exactly, while keeping noise and overhead to a minimum:

CREATE TABLE users (
   users_id    serial PRIMARY KEY
 , employee_nr int
 , is_employee bool NOT NULL DEFAULT false
 , CONSTRAINT role_employee CHECK (employee_nr IS NOT NULL = is_employee)  
 , UNIQUE (is_employee, users_id)  -- required for FK (otherwise redundant)
);

CREATE TABLE user_role (
   user_role_id serial PRIMARY KEY
 , users_id     int NOT NULL REFERENCES users
 , role_name    text NOT NULL
 , is_employee  bool CHECK(is_employee)
 , CONSTRAINT role_employee
   CHECK (role_name <> 'employee' OR is_employee IS TRUE)
 , CONSTRAINT role_employee_requires_employee_nr_fk
   FOREIGN KEY (is_employee, users_id) REFERENCES users(is_employee, users_id)
);

That's all.

These triggers are optional but recommended for convenience to set the added tags is_employee automatically and you don't have to do anything extra:

-- users
CREATE OR REPLACE FUNCTION trg_users_insup_bef()
  RETURNS trigger AS
$func$
BEGIN
   NEW.is_employee = (NEW.employee_nr IS NOT NULL);
   RETURN NEW;
END
$func$ LANGUAGE plpgsql;

CREATE TRIGGER insup_bef
BEFORE INSERT OR UPDATE OF employee_nr ON users
FOR EACH ROW
EXECUTE PROCEDURE trg_users_insup_bef();

-- user_role
CREATE OR REPLACE FUNCTION trg_user_role_insup_bef()
  RETURNS trigger AS
$func$
BEGIN
   NEW.is_employee = true;
   RETURN NEW;
END
$func$ LANGUAGE plpgsql;

CREATE TRIGGER insup_bef
BEFORE INSERT OR UPDATE OF role_name ON user_role
FOR EACH ROW
WHEN (NEW.role_name = 'employee')
EXECUTE PROCEDURE trg_user_role_insup_bef();

Again, no-nonsense, optimized and only called when needed.

SQL Fiddle demo for Postgres 9.3. Should work with Postgres 9.1+.

Major points

Now, if we want to set user_role.role_name = 'employee', then there has to be a matching user.employee_nr first.
You can still add an employee_nr to any user, and you can (then) still tag any user_role with is_employee, irregardless of the actual role_name. Easy to disallow if you need to, but this implementation does not introduce any more restrictions than required.
users.is_employee can only be true or false and is forced to reflect the existence of an employee_nr by the CHECK constraint. The trigger keeps the column in sync automatically. You could allow false additionally for other purposes with only minor updates to the design.
The rules for user_role.is_employee are slightly different: it must be true if role_name = 'employee'. Enforced by a CHECK constraint and set automatically by the trigger again. But it's allowed to change role_name to something else and still keep is_employee. Nobody said a user with an employee_nr is required to have an according entry in user_role, just the other way round! Again, easy to enforce additionally if needed.
If there are other triggers that could interfere, consider this:

How To Avoid Looping Trigger Calls In PostgreSQL 9.2.1

But we need not worry that rules might be violated because the above triggers are only for convenience. The rules per se are enforce with CHECK and FK constraints, which allow no exceptions.
Aside: I put the column is_employee first in the constraint UNIQUE (is_employee, users_id) for a reason. users_id is already covered in the PK, so it can take second place here:

DB associative entities and indexing

SQL Unique constraint across multiple tables

Add a 4th table specifically for these values you want to be unique then link these keys from this table into the others using a one to many relationship.
For example you will have the unique table with an ID, AppName and ItemName to make up its 3 columns. Then have this table link to the others.

For how to do this here is a good example
Create a one to many relationship using SQL Server

EDIT: This is what I would do but considering your server needs you can change what is needed:

CREATE TABLE AllItems(
    [id] [int] IDENTITY(1,1) NOT NULL,
    [itemType] [int] NOT NULL,
    [AppName] [nvarchar](20) NOT NULL,
    [ItemName] [nvarchar](32) NOT NULL,
    CONSTRAINT [pk_AllItems] PRIMARY KEY CLUSTERED ( [id] ASC )
) ON [PRIMARY]

CREATE TABLE Analog(
    [itemId] [int] NOT NULL,
    [Value] [float] NOT NULL
)

CREATE TABLE Discrete(
    [itemId] [int] NOT NULL,
    [Value] [bit] NOT NULL
)

CREATE TABLE Message(
    [itemId] [bigint] NOT NULL,
    [Value] [nvarchar](256) NOT NULL
)

ALTER TABLE [Analog] WITH CHECK 
    ADD CONSTRAINT [FK_Analog_AllItems] FOREIGN KEY([itemId])
REFERENCES [AllItems] ([id])
GO
ALTER TABLE [Analog] CHECK CONSTRAINT [FK_Analog_AllItems]
GO

ALTER TABLE [Discrete] WITH CHECK 
    ADD CONSTRAINT [FK_Discrete_AllItems] FOREIGN KEY([itemId])
REFERENCES [AllItems] ([id])
GO
ALTER TABLE [Discrete] CHECK CONSTRAINT [FK_Discrete_AllItems]
GO

ALTER TABLE [Message] WITH CHECK 
    ADD CONSTRAINT [FK_Message_AllItems] FOREIGN KEY([itemId])
REFERENCES [AllItems] ([id])
GO
ALTER TABLE [Message] CHECK CONSTRAINT [FK_Message_AllItems]
GO

From what I can tell your syntax is fine, I simply changed it to this way simply because I am more familiar with it but either should work.

Ms SQL Server Cross Table Constraint