![]() The constraint prevents both fks to be null at the same time, or both to be not null at the same time. Primary keys are important when designing database tables. There can be more UNIQUE columns, but only one primary key in a table. RfcF varchar(13) references clientF, - nullable by defaultĬlientM is null and clientF is not null or A table should have only one primary key, but multiple columns can be defined in a single primary key, when multiple columns are being used as a single. SQLite Primary key constraint The PRIMARY KEY constraint uniquely identifies each record in a database table. RfcM varchar(12) references clientM, - nullable by default In short, this is how I see it: clientPayment( You would need to remove the foreign key constraint, and that's a big no-no. However, you wouldn't be able to point to one or the other at will, since the foreign key constraint will enforce a non-null value to exist in both related tables always. It's technically possible to use a single column that stores a foreign key to both related tables. You probably want those columns to be nullable, so a row may "point" to one related table or the other (maybe even none, or both). I highly recommend you use two columns, one per each foreign key. int NOT NULL references dbo.Party(PartyId),įoreign Key to multiple tables at the same time PartyTypeId as cast(1 as tinyint) persisted,įoreign key (ID, PartyTypeId) references Party(PartyID, PartyTypeID) PartyTypeId as cast(2 as tinyint) persisted,įoreign key (ID, PartyTypeId) references Party(PartyId, PartyTypeID) PartyTypeId tinyint references dbo.PartyType(PartyTypeId), Heres a rough example using your posted schema: create table dbo.PartyType Or (my choice) model an entity that acts as a base for both Users and Groups, and have tickets owned by that entity. You could create a default group for every user and have tickets simply owned by either a true Group or a User's default Group. Perhaps in future you will want to allow a single ticket to be owned by multiple users or groups? This design does not enforce that a ticket must be owned by a single entity only. You could create M:M reference tables enabling both ticket:user and ticket:group relationships. You could simply create two columns in Ticket, OwnedByUserId and OwnedByGroupId, and have nullable Foreign Keys to each table. As always, the right design depends on your needs. You have a few options, all varying in "correctness" and ease of use. (CustomerEmployee INT NOT NULL PRIMARY KEY /* Same key identifies customer and employee */,įOREIGN KEY (CustomerEmployee) REFERENCES Customer (CustomerNum),įOREIGN KEY (CustomerEmployee) REFERENCES Employee (EmployeeNum)) This does assume that a common key is used to identify both customers and employees. Such a table might have a single foreign key that references both the Customer table and the Employee table. One possible example: a StaffDiscount table identifying customers who are also employees and are eligible for a discount. It is unusual in my experience but it potentially makes sense as a way to implement certain business rules with a meaning similar to "A must be both B and C". Here is the syntax to define ID attribute as a primary key in a COMPANY table.One foreign key referring to multiple table's primary keys Note: You would use these concepts while creating database tables. If a table has a primary key defined on any field(s), then you cannot have two records having the same value of that field(s). When multiple fields are used as a primary key, they are called a composite key. A primary key column cannot have NULL values.Ī table can have only one primary key, which may consist of single or multiple fields. A primary key is a field in a table which uniquely identifies the each rows/records in a database table. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |