Help DROP FOREING KEY constrain
Hello,
Need help on how to drop the FOREING KEY constrain from
a child table.
The example and the ALTER TABLE, which didn't work follows:
CREATE TABLE station
(
id INTEGER NOT NULL,
date_time DATETIME YEAR TO SECOND NOT NULL,
lat SMALLFLOAT NOT NULL,
lon SMALLFLOAT NOT NULL,
PRIMARY KEY (id)
);
INSERT INTO station VALUES(1, "1997-01-01 20:02:23", 1.0, 1.0);
INSERT INTO station VALUES(2, "1997-02-02 20:02:23", 2.0, 2.0);
INSERT INTO station VALUES(3,"1997-03-03 20:02:23", 3.0, 3.0);
CREATE TABLE p (
id INT NOT NULL,
d SMALLFLOAT NOT NULL,
t SMALLFLOAT NOT NULL,
FOREIGN KEY (id) REFERENCES station (id)
);
INSERT INTO p VALUES(1, 100.0, 10.0);
INSERT INTO p VALUES(2, 200.0, 20.0);
INSERT INTO p VALUES(3, 300.0, 30.0);
ALTER TABLE p DROP FOREIGN KEY (id) REFERENCES station (id);# ^
# 201: A syntax error has occurred.
Thanks!
Reyna
_________________________________________________________
Reyna Sabina Phone: (305) 361-4324
NOAA/AOML/PHOD Fax: (305) 361-4392
4301 Rickenbacker Causeway Email: Reyna.Sabina@noaa.gov
Miami, FL 33149-1087
"Things do not get better by being left alone."
-Winston Churchill
The foreign key was created without an explicit name so you will have to
look in the sysconstraints table to find the name that IDS generated for
it. It will be something like R_<tabid>_<constrid>:
select '>' || constrname || '<'
from systables st, sysconstraints sc
where st.tabname = 'p' and st.tabid = sc.tabid
and sc.constrtype = ''R';
ALTER TABLE p DROP CONSTRAINT <the constraint name you found>;
Art S. Kagel
Oninit
On Tue, May 13, 2008 at 2:11 PM, Reyna.Sabina <Reyna.Sabina@noaa.gov> wrote:
> Hello,
>
> Need help on how to drop the FOREING KEY constrain from
> a child table.
>
> The example and the ALTER TABLE, which didn't work follows:
>
> CREATE TABLE station
> (>
> id INTEGER NOT NULL,
>
> date_time DATETIME YEAR TO SECOND NOT NULL,
>
> lat SMALLFLOAT NOT NULL,
>
> lon SMALLFLOAT NOT NULL,
>
> PRIMARY KEY (id)
> );
>
> INSERT INTO station VALUES(1, "1997-01-01 20:02:23", 1.0, 1.0);
> INSERT INTO station VALUES(2, "1997-02-02 20:02:23", 2.0, 2.0);
> INSERT INTO station VALUES(3,"1997-03-03 20:02:23", 3.0, 3.0);>
> CREATE TABLE p (>
> id INT NOT NULL,
>
> d SMALLFLOAT NOT NULL,
>
> t SMALLFLOAT NOT NULL,
> FOREIGN KEY (id) REFERENCES station (id)
> );
>
> INSERT INTO p VALUES(1, 100.0, 10.0);
> INSERT INTO p VALUES(2, 200.0, 20.0);
> INSERT INTO p VALUES(3, 300.0, 30.0);>
> ALTER TABLE p DROP FOREIGN KEY (id) REFERENCES station (id);> # ^
> # 201: A syntax error has occurred.
>
> Thanks!
>
> Reyna
> _________________________________________________________
> Reyna Sabina Phone: (305) 361-4324
> NOAA/AOML/PHOD Fax: (305) 361-4392
> 4301 Rickenbacker Causeway Email: Reyna.Sabina@noaa.gov
> Miami, FL 33149-1087
>
> "Things do not get better by being left alone."
>
> -Winston Churchill
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>