Add default constraint from another table

Question

How to add a default constraint to a table such that the value that will be used as default will be from another table's columns?

i have created a table MY_TBL in which having a column CURRENCY_CD which references CURRENCY_CD column of table CURRENCY. Now i want to set MY_TBL.CURRENCY_CD default for a value coming from CURRENCY table.how can i achieve this?

• ypercubeᵀᴹ: Why not use a constant?
• Rohaan: @ypercube: because the value may change, if modified via master screen

Answer 1

You can use an INSERT trigger and select the value from the other table as the value for this column. This will be done for each insert so this is at least a performance issue.

I'm afraid this is not a very good solution because this is intransparent. There would be better solutions with a better architecture. Maybe this is rather a client software task.

Answer 2

If I understand the question correctly, one option would be to use a scalar function, which selects a value from your currency table to use as the default value for my_table's currency column.

To achieve this, you'd need to:

• Add a new column to your CURRENCY table, something like IS_DEFAULT (with a BIT type),
• Create a scalar function, which selects the default CURRENCY_ID from the CURRENCY table (i.e. WHERE IS_DEFAULT = 1)
• Call the scalar function when creating your default constraint

Here's a full example:

-- create currency table
CREATE TABLE dbo.currency (
 currency_id INT NOT NULL IDENTITY(1,1) PRIMARY KEY, 
 currency_short_code VARCHAR(3),
 is_default BIT NOT NULL DEFAULT (0) -- need to know which currency to use as the default value
)
GO
-- add some currencies to our currency table
INSERT INTO dbo.currency (currency_short_code, is_default)
VALUES
 ('GBP', 1), -- this will be our default currecny to use
 ('USD', 0)
GO
-- function to select the default currency
CREATE FUNCTION dbo.get_default_currency ()
RETURNS INT
AS
BEGIN
 RETURN (SELECT TOP 1 currency_id FROM dbo.currency WHERE is_default = 1);
END;
GO
-- table that references the currency table
CREATE TABLE dbo.my_table (
 my_table_id INT NOT NULL IDENTITY(1,1) PRIMARY KEY, 
 some_value VARCHAR(20),
 currency_id INT NOT NULL FOREIGN KEY REFERENCES dbo.currency(currency_id) DEFAULT(dbo.get_default_currency())
 -- our default constraint above calls the function to get the default currency
);
GO
-- add some data to our table
INSERT INTO dbo.my_table (some_value, currency_id)
VALUES
 ('first test', 2) -- note, here we're explicitly stating which currency_id to use, which is the non-default option (USD)
SELECT * FROM dbo.my_table WHERE some_value = 'first test' 
-- returns 1, first test, 2
INSERT INTO dbo.my_table (some_value)
VALUES
 ('second test') -- note, here we do not provide a currency_id, so the default one should be used (GBP)
SELECT * FROM dbo.my_table WHERE some_value = 'second test' 
-- returns 2, second test, 1