FR, SQL backend and Foreign keys

[Fabien COMBERNOUS]

On 28/09/2011 11:45, Alan DeKok wrote:
> Fabien COMBERNOUS wrote:
>> here a patch proposed :
>    A bit of explanation would help.
Foreign key : add a username in radusergroup table needs to exist in 
radcheck table and be equal.
On cascade : if you update ou delete a username in radcheck table, the 
same action is done in radusergroup table.
>
>    It looks reasonable, but I'd want someone to try it before putting it
> into the server.
>
>> --- schema_orig.sql    2011-09-28 10:42:08.000000000 +0200
>    *PLEASE* use full paths.  There are 4-5 SQL backends in the server.
> Which one is this modifying?  Don't make us guess...
>
>
>> +++ schema_withFK.sql    2011-09-28 10:46:42.000000000 +0200
>> @@ -63,8 +63,8 @@
>>     op char(2) NOT NULL DEFAULT '==',
>>     value varchar(253) NOT NULL default '',
>>     PRIMARY KEY  (id),
>> -  KEY username (username(32))
>> -) ;
>> +  KEY username (username)
>> +) ENGINE=InnoDB;
>>
>>   #
>>   # Table structure for table 'radgroupcheck'
>> @@ -117,8 +117,10 @@
>>     username varchar(64) NOT NULL default '',
>>     groupname varchar(64) NOT NULL default '',
>>     priority int(11) NOT NULL default '1',
>> -  KEY username (username(32))
>> -) ;
>> +  INDEX username (username),
>> +  FOREIGN KEY (`username`) REFERENCES radcheck(username)
>> +    ON DELETE CASCADE ON UPDATE CASCADE
>> +) ENGINE=InnoDB;
>>
> -
> List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
>
>


-- 
*Fabien COMBERNOUS*
/unix system engineer/
www.kezia.com <http://www.kezia.com/>
*Tel: +33(0)9 5279 5202*
Kezia