PostgreSQL: How to implement minimum cardinality?
There's no way to specify this using a CHECK constraint, so I think the best approach is a trigger:
http://www.postgresql.org/docs/9.1/static/sql-createtrigger.html
http://www.postgresql.org/docs/9.1/static/plpgsql-trigger.html
You'd end up with something like (I haven't tested it or anything):
CREATE TRIGGER at_least_one before INSERT, UPDATE, DELETE ON the_one_table FOR EACH ROW EXECUTE PROCEDURE check_at_least_one();CREATE OR REPLACE FUNCTION check_at_least_one() RETURNS trigger AS $$ BEGIN nmany := select count(*) from the_many_table where the_many_table.the_one_id=NEW.id; IF nmany > 0 THEN RETURN NEW; END IF; RETURN NULL;END;
There is no way to enforce such rules using only FOREIGN KEY constraints.
1) One way is by allowing circular references between tables (the "default" column, advised by jug). This results in chicken-and-egg problems that are difficult to manage, you'll have to use deferrable constraints. Plus, this option is simply not available in some DBMS. Another disadvantage is that for a football team, you'll have to add 11 "default" columns (and you'll have to deal with a chicken-and-11-eggs problem)!
2) Another option is to use triggers.
3) Another option is to use a database-level constraint between the 2 tables. Not sure if there is any DBMS that has such functionality. Besides the typical UNIQUE, PRIMARY and FOREIGN KEY constraints, most DBMS have just row level constraints and with limitations (no subqueries, etc).
4) Another option is to enforce such rules by creating appropriate INSERT, UPDATE and DELETE procedures that can only access the two related tables and enforce the integrity according to these rules. This is the better approach (in my opinion).
5) One more option, simpler to implement is to use standard Foreign Key constraints, enforcing the 1-to-many relationship and have a View that shows those Teams that actually have 11 or more players. This off course mean sthat you don't actually enforce the rules you ask for. But it's possible (and may I say probable) that you can afford not too. If the football players get killed in an accident for example, the team can not longer play in tournaments but it's still a team. So, you may define two entities, the Team (the base Table) and the ProperTeam (View), that can play games. Example:
CREATE VIEW AS ProperTeam( SELECT * FROM Team WHERE ( SELECT COUNT(*) FROM Player WHERE Player.TeamId = Team.TeamId ) >= 11) Options 1 and 2 look rather "messy" but that's only a personal opinion, many people like triggers.
I would choose Option 4, unless I can ("cheat" and) actually not enforce the constraint with Option 5.
If you have the addresses in one table adresses, you can define a column "default_address" (in the table customers) that is a non-null foreign key reference to the address that must be provided.
If you have a table shippings that provides an optional many-to-many relationship by referencing a person, an address and maybe an order(-item), then you could use coalesce to overwrite the NULLs for addresses you get in an outer join between (customers inner join orders) and shipping_addresses (a view joining shippings with addresses). But to prevent problems with maybe different numbers of non-null components of addresses, Stephane Faroult recommends in his (strongly recommended!) book The Art of SQL to use the "hidden sort key" technique (assuming that customers_with_default_address is a view joining customers with addresses using "default_address":
select * from (select 1 as sortkey, line_1, line_2, city, state, postal_code, country from shipping_addresses where customer_id = ? union select 2 as sortkey, line_1, line_2, city, state, postal_code, country from customers_with_default_address where customer_id = ? order by 1) actual_shipping_address limit 1