SQL Server 2012 – How to Add a Column to an Existing Table

Here are two best practices for partitioning that pertain to the question:

1. Keep an empty staging partition at the leftmost and rightmost ends of the partition range to ensure that the partitions split when loading in new data, and merge, after unloading old data, do not cause data movement.
2. Do not split or merge a partition already populated with data because this can cause severe locking and explosive log growth.

http://www.informit.com/articles/article.aspx?p=1946159&seqNum=5

If the leftmost end of your partition is empty, use ALTER PARTITION FUNCTION SPLIT RANGE to add new ranges to the partition function.

To check if the leftmost partition is empty, use a query like the following:

DECLARE @PartitionFunctionName sysname = 'YourPartitionFunctionNameHere';

SELECT 
p.partition_number, SUM(pst.row_count) RowCountInPartition, pf.name PartitionFunction, ps.name PartitionScheme
FROM sys.dm_db_partition_stats pst
INNER JOIN sys.partitions p ON pst.partition_id = p.partition_id
INNER JOIN sys.indexes i ON p.object_id = i.object_id AND p.index_id = i.index_id
INNER JOIN sys.partition_schemes ps ON ps.data_space_id = i.data_space_id
INNER JOIN sys.partition_functions pf ON ps.function_id = pf.function_id
WHERE pf.name = @PartitionFunctionName
GROUP BY p.partition_number, pf.name, ps.name;

If the first partition is not empty, the best practices recommend that you create a new function with all values, create a new table on that function, then insert the data to the new table.

Also, if the left partition just has a few records, a split may be fine. Not sure on that as I've never tried it.

Whatever you do, make sure to leave some empty partitions at the leftmost and rightmost partition when you're finished. I might even go so far as to creating partition ranges for 0 and 1, then add a check constraint to prevent the first partition from getting data in it. Do the same thing for the end.

Yes, you can do this with a COMPUTED column, It's a workaround really as the column has to be PERSISTED to be used for the foreign key constraint so it consumes storage space:

CREATE TABLE dbo.OtherTable (
    ID INT PRIMARY KEY IDENTITY(1,1),
    Blah VARCHAR(100),
    FieldName AS CAST('This' AS VARCHAR(100)) PERSISTED NOT NULL
) ;

ALTER TABLE dbo.OtherTable WITH CHECK 
  ADD CONSTRAINT [FK__OtherTable__Blah] 
    FOREIGN KEY (FieldName, Blah) 
    REFERENCES Lookup (FieldName, Value) ;

Tested at SQL-Fiddle.

The table can then be used as if the FieldName does not exist. You could even define a view that does not include this column and make your applications use that view: SQL-Fiddle-2

The syntax you hoped for, although looking pretty, has not been implemented in any DBMS I know of:

ALTER TABLE dbo.OtherTable WITH CHECK 
  ADD CONSTRAINT [FK__OtherTable__Blah] 
    FOREIGN KEY('This', Blah) 
    REFERENCES Lookup (FieldName, Value) ;

Note however that the workaround with the computed columns would be 100% equivalent to this syntax, if only SQL-Server removed in the future the restriction of using only persisted columns. You could add a Connect item with your request.

For the question whether there is any other DBMS that allows this, no, there isn't. Check also a related question:

Are there DBMS that allow a Foreign Key that References a View (and not only base tables)?

If you really don't want to use this method (because of the additional storage requirements, alternative paths would be to enforce the constraint via procedures or triggers (Note that you would have to write procedures or triggers for both tables involved.)