Which RDBMS checks 1-1 relationship validity?

[Marek Kotowski]

I mean: which RDBMS

1) allows to declare that relationship is in fact 1-1, and
2) protests when one tries to violate the 1-1 relationship,
that is inserts into a table two records with the same
foreign key?

Thank you in advance.

Marek Kotowski
Warsaw

[Bill Karwin]

Marek Kotowski wrote:

I mean: which RDBMS

1) allows to declare that relationship is in fact 1-1, and
2) protests when one tries to violate the 1-1 relationship,
that is inserts into a table two records with the same
foreign key?

Create a unique index on your foreign key field.

And remember that in MySQL enforces foreign key references only if the
table is stored as an InnoDB table.

Regards,
Bill K.

[Marek Kotowski]

Bill Karwin <bi**@karwin.com> wrote in message news:<ck********@enews2.newsguy.com>...

Thank you for the answer.

Create a unique index on your foreign key field.
I was thinking about specifing 1-1 relationship
in CREATE or ALTER TABLE.

And remember that in MySQL enforces foreign key references only if the
table is stored as an InnoDB table.

I know that. AFAIK in 5.0 MyISAM will have the RI enforcing option
as well.

Regards

Marek Kotowski
Warsaw

[Bill Karwin]

Marek Kotowski wrote:

I was thinking about specifing 1-1 relationship
in CREATE or ALTER TABLE.

You _can_ say "CONSTRAINT UNIQUE (columnName)" in a CREATE or ALTER
TABLE statement.
Read about it at: http://dev.mysql.com/doc/mysql/en/CREATE_TABLE.html

But I think I know what you mean, you want some other syntax that is
specific to 1-1 relationships. No, I don't think there is such syntax.

Regards,
Bill K.

[Marek Kotowski]

Bill Karwin <bi**@karwin.com> wrote in message news:<ck*********@enews3.newsguy.com>...

Marek Kotowski wrote:

I was thinking about specifing 1-1 relationship
in CREATE or ALTER TABLE.
You _can_ say "CONSTRAINT UNIQUE (columnName)" in a CREATE or ALTER
TABLE statement.
Read about it at: http://dev.mysql.com/doc/mysql/en/CREATE_TABLE.html

But I think I know what you mean, you want some other syntax that is
specific to 1-1 relationships.

Exactly.
No, I don't think there is such syntax.
It'a pity. May be next SQL standard will propose
such formal syntax.

Regards

Marek Kotowski
Warsaw
Regards,
Bill K.

[Bill Karwin]

Marek Kotowski wrote:

No, I don't think there is such syntax.

It'a pity. May be next SQL standard will propose
such formal syntax.

Well, there's no SQL syntax that explicitly declares a one-to-many or a
many-to-many relationship either. SQL has language the describe the
physical implementation of a relational database. What you're
describing is like an Entity Relationship diagram that describes the
_logical_ relationships, and then enables you to generates a database
based on that.

In fact, there exist several software products that do this.
For instance:
http://solutions.mysql.com/search.php?q=diagram

Regards,
Bill K.