FOREIGN KEY Reference on multiple columns - Mailing list pgsql-sql
| From | Weiss, Jörg |
|---|---|
| Subject | FOREIGN KEY Reference on multiple columns |
| Date | |
| Msg-id | 4B4E89127868BD458A795430BCF4FD1328C51A6F@DVZSN-RA0325.bk.dvz-mv.net Whole thread Raw |
| Responses |
Re: FOREIGN KEY Reference on multiple columns
|
| List | pgsql-sql |
<div class="WordSection1"><p class="MsoNormal">Hi!<p class="MsoNormal"> <p class="MsoNormal"><span lang="EN-US" style="mso-ansi-language:EN-US">Isit possible to create a “FOREIGN KEY CONSTRAINT” with references to multiple columns ofthe reference table?</span><p class="MsoNormal"><span lang="EN-US" style="mso-ansi-language:EN-US"> </span><p class="MsoNormal"><spanlang="EN-US" style="mso-ansi-language:EN-US">For example:</span><p class="MsoNormal"><span lang="EN-US"style="mso-ansi-language:EN-US">Table <span class="SpellE"> parm</span>:</span><p class="MsoNormal"><span lang="EN-US"style="mso-ansi-language:EN-US">CREATE TABLE <span class="SpellE">parm</span></span><p class="MsoNormal"><spanlang="EN-US" style="mso-ansi-language:EN-US">(</span><p class="MsoNormal"><span lang="EN-US" style="mso-ansi-language:EN-US"><spanstyle="mso-spacerun:yes"> </span>complex <span class="SpellE">varchar</span>(20) NOTNULL,</span><p class="MsoNormal"><span lang="EN-US" style="mso-ansi-language:EN-US"><span style="mso-spacerun:yes"> </span>para<span class="SpellE">varchar</span>(50) NOT NULL,</span><p class="MsoNormal"><span lang="EN-US" style="mso-ansi-language:EN-US"><spanstyle="mso-spacerun:yes"> </span>sort <span class="SpellE">int4</span> NOT NULL DEFAULT10,</span><p class="MsoNormal"><span style="mso-spacerun:yes"> </span><span class="SpellE">value</span> <span class="SpellE">varchar</span>(50)NULL,<span lang="EN-US" style="mso-ansi-language:EN-US"></span><p class="MsoNormal"><spanlang="EN-US" style="mso-ansi-language:EN-US"><span style="mso-spacerun:yes"> </span>CONSTRAINT <spanclass="SpellE">parm_pkey</span> PRIMARY KEY (complex, para, sort)</span><p class="MsoNormal"><span lang="EN-US" style="mso-ansi-language:EN-US">)</span><pclass="MsoNormal"><span lang="EN-US" style="mso-ansi-language:EN-US"> </span><pclass="MsoNormal"><span lang="EN-US" style="mso-ansi-language:EN-US">Table user</span><p class="MsoNormal"><span lang="EN-US" style="mso-ansi-language:EN-US">CREATE TABLE user</span><p class="MsoNormal"><spanlang="EN-US" style="mso-ansi-language:EN-US">(</span><p class="MsoNormal"><span lang="EN-US" style="mso-ansi-language:EN-US"><spanstyle="mso-spacerun:yes"> </span>name <span class="SpellE">varchar</span>(20) NOT NULL,</span><pclass="MsoNormal"><span lang="EN-US" style="mso-ansi-language:EN-US"><span style="mso-spacerun:yes"> </span>typeinteger NULL</span><p class="MsoNormal"><span lang="EN-US" style="mso-ansi-language:EN-US">)</span><p class="MsoNormal"><spanlang="EN-US" style="mso-ansi-language:EN-US"> </span><p class="MsoNormal"><span lang="EN-US" style="mso-ansi-language:EN-US">NowI want to create FOREIGN KEY on <span class="SpellE">user.type</span> with referenceson <span class="SpellE">parm.value</span> and <span class="SpellE">param.para</span> WHERE <span class="SpellE">param.para</span>= ‘<span class="SpellE">user_type</span>’</span><p class="MsoNormal"><span lang="EN-US" style="mso-ansi-language:EN-US">Somethinglike this:</span><p class="MsoNormal"><span lang="EN-US" style="mso-ansi-language:EN-US">ALTERTABLE user ADD CONSTRAINT <span class="SpellE">user_type_fkey</span> FOREIGN KEY (type)REFERENCES <span class="SpellE"> parm</span> (<span class="SpellE">value,para</span>) WHERE <span class="SpellE">parm.para</span>= '<span class="SpellE">user_type</span>';</span><p class="MsoNormal"><span lang="EN-US" style="mso-ansi-language:EN-US"> </span><pclass="MsoNormal"><span lang="EN-US" style="mso-ansi-language:EN-US">Regards …</span><pclass="MsoNormal"><span lang="EN-US" style="mso-ansi-language:EN-US"> </span><p class="MsoNormal"><span lang="EN-US"style="mso-ansi-language:EN-US"> </span></div>