Foreign key allow null

a?§??ú

Delphi Developer

Wed, 18 Jun 1902 08:00:00 GMT

Foreign key allow null

Dear All :

I have a problem between Delphi4 and SQL7.0 .

I created two tables and relations :

CREATE TABLE type (
type varchar(2) NOT NULL,
con varchar(2) NOT NULL
)
go

ALTER TABLE type
ADD PRIMARY KEY (type)
go

CREATE TABLE prod (
prodid varchar(2) NOT NULL,
type varchar(2) NULL
)
go

ALTER TABLE prod
ADD PRIMARY KEY (prodid)
go

= = = = = = = = = =
ALTER TABLE prod
ADD FOREIGN KEY (type)
REFERENCES type
go

========================================
The Foreign Key set to allow NULL .

I used TTable,DBNavigator,two DBEdits for insert and edit table "prod"
with fields "prodid" and "type" ;

but when I edit prod ; Delete type.Text on DBEdit.

There is a problem after posting ;

Error message: Master record missing ;

I found that the type='' rather than type is NULL to SQL Server from SQL
Monitor .

So,SQL Server can't find type='' from type table .

Then I executed SQL command (update prod set type = null where
prodid='A' ) in SQL explorer,having no problems.

Is there solution I can solve this problem ?

Any help would be greate !

Thanks .

Thomas Friedman

Delphi Developer

Wed, 18 Jun 1902 08:00:00 GMT

Re:Foreign key allow null

You can use Query.Fieldname.clear to set a explicit NULL Value.

Thomas Friedmann
friedm...@idee-gmbh.de

a??? <hungm...@tomail.com.tw> schrieb in im Newsbeitrag:
3873F8FD.8FDAC...@tomail.com.tw...

Quote

> Dear All :

> I have a problem between Delphi4 and SQL7.0 .

> I created two tables and relations :

> CREATE TABLE type (
> type varchar(2) NOT NULL,
> con varchar(2) NOT NULL
> )
> go

> ALTER TABLE type
> ADD PRIMARY KEY (type)
> go

> CREATE TABLE prod (
> prodid varchar(2) NOT NULL,
> type varchar(2) NULL
> )
> go

> ALTER TABLE prod
> ADD PRIMARY KEY (prodid)
> go

> = = = = = = = = = =
> ALTER TABLE prod
> ADD FOREIGN KEY (type)
> REFERENCES type
> go

> ========================================
> The Foreign Key set to allow NULL .

> I used TTable,DBNavigator,two DBEdits for insert and edit table "prod"
> with fields "prodid" and "type" ;

> but when I edit prod ; Delete type.Text on DBEdit.

> There is a problem after posting ;

> Error message: Master record missing ;

> I found that the type='' rather than type is NULL to SQL Server from SQL
> Monitor .

> So,SQL Server can't find type='' from type table .

> Then I executed SQL command (update prod set type = null where
> prodid='A' ) in SQL explorer,having no problems.

> Is there solution I can solve this problem ?

> Any help would be greate !

> Thanks .

hungmin

Delphi Developer

Wed, 18 Jun 1902 08:00:00 GMT

Re:Foreign key allow null

I found that using Oracle data type varchar2 has no problem .
So,I want to find the way to solve this problem without coding before post ;

Thomas Friedmann ?g1D?G

Quote

> You can use Query.Fieldname.clear to set a explicit NULL Value.

> Thomas Friedmann
> friedm...@idee-gmbh.de

> a??? <hungm...@tomail.com.tw> schrieb in im Newsbeitrag:
> 3873F8FD.8FDAC...@tomail.com.tw...
> > Dear All :

> > I have a problem between Delphi4 and SQL7.0 .

> > I created two tables and relations :

> > CREATE TABLE type (
> > type varchar(2) NOT NULL,
> > con varchar(2) NOT NULL
> > )
> > go

> > ALTER TABLE type
> > ADD PRIMARY KEY (type)
> > go

> > CREATE TABLE prod (
> > prodid varchar(2) NOT NULL,
> > type varchar(2) NULL
> > )
> > go

> > ALTER TABLE prod
> > ADD PRIMARY KEY (prodid)
> > go

> > = = = = = = = = = =
> > ALTER TABLE prod
> > ADD FOREIGN KEY (type)
> > REFERENCES type
> > go

> > ========================================
> > The Foreign Key set to allow NULL .

> > I used TTable,DBNavigator,two DBEdits for insert and edit table "prod"
> > with fields "prodid" and "type" ;

> > but when I edit prod ; Delete type.Text on DBEdit.

> > There is a problem after posting ;

> > Error message: Master record missing ;

> > I found that the type='' rather than type is NULL to SQL Server from SQL
> > Monitor .

> > So,SQL Server can't find type='' from type table .

> > Then I executed SQL command (update prod set type = null where
> > prodid='A' ) in SQL explorer,having no problems.

> > Is there solution I can solve this problem ?

> > Any help would be greate !

> > Thanks .

Other Threads

1. Null values and foreign keys

2. Simple DBGrid Problem trying to Null out Foreign Keys

3. Interbase:Foreign key: null value

4. Looking Up NULL Foreign Key

5. null in foreign key in DB2?

6. Modify Key Field ( when it's foreign key in another table)

7. Foreign keys and Secondary keys

8. Allowing NULL values when creating DB

9. Field with NULL allow

10. Does the column allow nulls?