StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSQL Server 2005 FOREIGN KEY that references a "constant" value for a second column
text
Body
copied!<p>I have a table that has a foreign key to another table.<br> For example: <code>Postion.day REFERENCES weekdays.day</code>which is fine. However <code>Position.day</code> can hold weekdays where <code>rdo=true</code>. </p> <p>The primary way of accessing this data is planned to be through a Web Application, that I'm witting. I plan on adding this check in the web-application anyway. I'm just looking for way to enforce as much data integrity at the DB level as I can short of writing triggers.</p> <p>I suspect the answer to look something like: </p> <pre><code>ALTER TABLE Postition ADD COLUMN day CHAR(3) FOREIGN KEY REFERENCES weekday(shortName) CHECK (weekday.rdo=TRUE); </code></pre> <p>Normally I would "try it and see", however, I have a lot of changes to make and I'm still finalizing my design and thought I would ask the experts and see what they had to say while I worked on the rest of it.</p> <p><strong>UPDATE:</strong><br> ok So I have a table, <code>Name Table</code>(I didn't name it), I have another Table weekdays which lists All 7 days of the week along with some other info. Name Table has 2 Foreign Keys rdo and shortDay. weekdays holds a bit field for rdo and and a bit field for shortday stating if the day is eligible to used for those days. So I want my RDO field to be a foreign key to weekdays but ONLY WHERE RDO=TRUE. </p> <p>Weekdays primary Key is shortname, 3 letters( char(3) ) representing a weekday, EG: Mon, Tue, Wed, Thu, etc</p> <p>I was thinking about it and remembered SQL transactions.(I'm currently betting that SQL Server will be smart enough to rollback a successful ALTER TABLE call, the position table already exists.) </p> <pre><code>BEGIN ALTER TABLE [Name Table] ADD RDO CHAR(3); ALTER TABLE [Name Table] ADD FOREIGN KEY (RDO) REFERENCES weekdays(shortName); ALTER TABLE [Name Table] ADD CHECK (TRUE=(SELECT rdo FROM weekdays WHERE shortName=RDO)); ROLLBACK; </code></pre> <p>Which returns from the Database: </p> <blockquote> <p>Error code 102, SQL state S0001: Incorrect syntax near ')'.<br> Line 1, column 1</p> <p>Error code 1769, SQL state S0001: Foreign key 'RDO' references invalid column 'RDO' in referencing table 'Name Table'.<br> Line 3, column 1</p> <p>Error code 1046, SQL state S0001: Subqueries are not allowed in this context. Only scalar expressions are allowed.<br> Line 4, column 1</p> <p>Error code 3903, SQL state S0001: The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.<br> Line 5, column 1 </p> </blockquote> <p>So Adding the foreign Key is easy enough but I'm still stumped on how to reference the linked date in another table inside of a check constraint. </p> <p>Ideally the Syntax would look like this(which I know is invalid): </p> <pre><code>ALTER TABLE [Name Table] ADD RDO CHAR(3) FOREIGN KEY REFERENCES weekdays(shortName,rdo=true); </code></pre> <p>does that help?</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload